﻿/* 
	Returns the identity of the root folder for a specific data type
*/
IF (object_id('dbo.GetRootFolderID')) IS NOT NULL
	DROP PROCEDURE dbo.GetRootFolderID
GO
CREATE PROCEDURE dbo.GetRootFolderID
(
        @ObjectType varchar(50),
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT  @OutIdentity = DefFolderID
		FROM [dbo].[DefFolder]
			WITH (NOLOCK)
		WHERE Active=1 AND ObjectType = @ObjectType
		
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Simply creates a new record and returns the identity
*/
IF (object_id('dbo.PortalAddTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestCase
GO
CREATE PROCEDURE dbo.PortalAddTestCase
(
        @TestCaseName varchar(255),
        @QCID varchar(10) = NULL,
		@BaseState varchar(255) = NULL,
		@MaxExecutionTimeMins int = NULL,
        @Description varchar(1024) = NULL,
        @ImportedBy varchar(255) = NULL,
		@ImportedDate datetime = NULL,
		@FolderID int = NULL,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Reset older test case if one exists
	--- UPDATE dbo.DefTestCase SET [Active]=0 WHERE [Active]=1 AND [TestCaseName]=@TestCaseName

	--- Check for valid TestCase Name
	IF '' = RTRIM(@TestCaseName)
	BEGIN
		RAISERROR('Test Case Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	--- Nullify any passed parameters that are empty strings
	IF @BaseState IS NOT NULL
		IF '' = RTRIM(@BaseState)
			SET @BaseState = NULL
	IF @QCID IS NOT NULL
		IF '' = RTRIM(@QCID)
			SET @QCID = NULL
	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @ImportedBy IS NOT NULL
		IF '' = RTRIM(@ImportedBy)
			SET @ImportedBy = NULL
	IF @ImportedDate IS NULL
		SET @ImportedDate = GETDATE()
	IF @FolderID IS NULL
		EXEC GetRootFolderID @ObjectType = 'DefTestCase', @OutIdentity = @FolderID OUTPUT
	--- Insert
	INSERT INTO [dbo].[DefTestCase]
			([DefFolderID]
			,[TestCaseName]
			,[QCID]
			,[Description]
			,[BaseState]
			,[MaxExecutionTimeMins]
			,[Active]
			,[LastUpdatedDate]
			,[LastUpdatedBy]
			)
		VALUES
			(@FolderID
			,@TestCaseName
			,@QCID
			,@Description
			,@BaseState
			,@MaxExecutionTimeMins
			,0
			,@ImportedDate
			,@ImportedBy
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Adds a step to a test case along with parameters
*/
IF (object_id('dbo.PortalAddTestStep')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestStep
GO
CREATE PROCEDURE dbo.PortalAddTestStep
(
        @TestCaseID int,
        @StepName varchar(255),
		@ActionWordName varchar(255),
        @SeqNo int = 0,
		@ParamName01 varchar(255) = NULL, @ParamData01 varchar(2048) = NULL,
		@ParamName02 varchar(255) = NULL, @ParamData02 varchar(2048) = NULL,
		@ParamName03 varchar(255) = NULL, @ParamData03 varchar(2048) = NULL,
		@ParamName04 varchar(255) = NULL, @ParamData04 varchar(2048) = NULL,
		@ParamName05 varchar(255) = NULL, @ParamData05 varchar(2048) = NULL,
		@ParamName06 varchar(255) = NULL, @ParamData06 varchar(2048) = NULL,
		@ParamName07 varchar(255) = NULL, @ParamData07 varchar(2048) = NULL,
		@ParamName08 varchar(255) = NULL, @ParamData08 varchar(2048) = NULL,
		@ParamName09 varchar(255) = NULL, @ParamData09 varchar(2048) = NULL,
		@ParamName10 varchar(255) = NULL, @ParamData10 varchar(2048) = NULL,
		@ParamName11 varchar(255) = NULL, @ParamData11 varchar(2048) = NULL,
		@ParamName12 varchar(255) = NULL, @ParamData12 varchar(2048) = NULL,
		@ParamName13 varchar(255) = NULL, @ParamData13 varchar(2048) = NULL,
		@ParamName14 varchar(255) = NULL, @ParamData14 varchar(2048) = NULL,
		@ParamName15 varchar(255) = NULL, @ParamData15 varchar(2048) = NULL,
		@ParamName16 varchar(255) = NULL, @ParamData16 varchar(2048) = NULL,
		@ParamName17 varchar(255) = NULL, @ParamData17 varchar(2048) = NULL,
		@ParamName18 varchar(255) = NULL, @ParamData18 varchar(2048) = NULL,
		@ParamName19 varchar(255) = NULL, @ParamData19 varchar(2048) = NULL,
		@ParamName20 varchar(255) = NULL, @ParamData20 varchar(2048) = NULL
)
AS
BEGIN
	SET NOCOUNT ON
	
	--- Check for valid TestCase ID
	---PRINT 'Check for valid TestCase ID'
	IF NOT EXISTS ( SELECT 1 FROM dbo.DefTestCase WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID )
	BEGIN
		RAISERROR('Referenced TestCaseID does not exist in dbo.DefTestCase', 18, 1)
		IF @@Error != 0 
			RETURN 
	END
	--- Check for valid TestCase ID (should not be active yet)
	---PRINT 'Check for valid TestCase ID (should not be active yet)'
	IF EXISTS ( SELECT 1 FROM dbo.DefTestCase WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID AND Active=1 )
	BEGIN
		RAISERROR('Referenced TestCaseID is currently active. You cannot modify an active TestCase.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END
	
	DECLARE @ActionWordID int
	IF NOT EXISTS ( SELECT 1 FROM dbo.ActionWords WITH (NOLOCK) WHERE ActionWordName = @ActionWordName AND Active=1 )
	BEGIN
		--- Invalid action word, throw error
		RAISERROR('Referenced ActionWordName does not exist in dbo.ActionWords', 18, 1)
		IF @@Error != 0 
			RETURN 
	END
	--- Get the ActionWordID
	SELECT @ActionWordID = ActionWordID FROM dbo.ActionWords (NOLOCK) WHERE ActionWordName = @ActionWordName AND Active=1
	--- Validate Sequence Number
	IF @SeqNo = 0
	BEGIN
		IF NOT EXISTS ( SELECT 1 FROM dbo.DefTestStep WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID )
			SET @SeqNo = 1
		ELSE
			SELECT @SeqNo = (MAX(SeqNo) + 1) FROM dbo.DefTestStep WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID
	END
	ELSE
		IF NOT EXISTS ( SELECT 1 FROM dbo.DefTestStep WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID AND SeqNo = @SeqNo )
			BEGIN
				--- Invalid sequence!
				RAISERROR('Step SeqNo already exists in dbo.DefTestStep', 18, 1)
				IF @@Error != 0 
					RETURN 
			END

	DECLARE @TestStepID int
	--- Insert Step Record
	---PRINT 'Insert Step Record'
	INSERT INTO [dbo].[DefTestStep]
			([DefTestCaseID]
			,[StepName]
			,[ActionWordID]
			,[SeqNo]
			)
		VALUES
			(@TestCaseID
			,@StepName
			,@ActionWordID
			,@SeqNo
			)
	--- Get identity
	---PRINT 'Get identity'
	SELECT @TestStepID = SCOPE_IDENTITY()
	---PRINT @TestStepID

	--- Insert the parameters
	---PRINT 'Insert the parameters'
	--- @ParamName01
	---PRINT @ParamName01
	IF @ParamName01 IS NULL OR '' = RTRIM(@ParamName01)
		RETURN
	---PRINT 'Inserting @ParamName01'
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,1
			,@ParamName01
			,@ParamData01
			)
	--- @ParamName02
	---PRINT @ParamName02
	IF @ParamName02 IS NULL OR '' = RTRIM(@ParamName02)
		RETURN
	---PRINT 'Inserting @ParamName02'
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,2
			,@ParamName02
			,@ParamData02
			)
	--- @ParamName03
	IF @ParamName03 IS NULL OR '' = RTRIM(@ParamName03)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,3
			,@ParamName03
			,@ParamData03
			)
	--- @ParamName04
	IF @ParamName04 IS NULL OR '' = RTRIM(@ParamName04)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,4
			,@ParamName04
			,@ParamData04
			)
	--- @ParamName05
	IF @ParamName05 IS NULL OR '' = RTRIM(@ParamName05)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,5
			,@ParamName05
			,@ParamData05
			)
	--- @ParamName06
	IF @ParamName06 IS NULL OR '' = RTRIM(@ParamName06)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,6
			,@ParamName06
			,@ParamData06
			)
	--- @ParamName07
	IF @ParamName07 IS NULL OR '' = RTRIM(@ParamName07)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,7
			,@ParamName07
			,@ParamData07
			)
	--- @ParamName08
	IF @ParamName08 IS NULL OR '' = RTRIM(@ParamName08)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,8
			,@ParamName08
			,@ParamData08
			)
	--- @ParamName09
	IF @ParamName09 IS NULL OR '' = RTRIM(@ParamName09)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,9
			,@ParamName09
			,@ParamData09
			)
	--- @ParamName10
	IF @ParamName10 IS NULL OR '' = RTRIM(@ParamName10)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,10
			,@ParamName10
			,@ParamData10
			)
	--- @ParamName11
	IF @ParamName11 IS NULL OR '' = RTRIM(@ParamName11)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,11
			,@ParamName11
			,@ParamData11
			)
	--- @ParamName12
	IF @ParamName12 IS NULL OR '' = RTRIM(@ParamName12)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,12
			,@ParamName12
			,@ParamData12
			)
	--- @ParamName13
	IF @ParamName13 IS NULL OR '' = RTRIM(@ParamName13)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,13
			,@ParamName13
			,@ParamData13
			)
	--- @ParamName14
	IF @ParamName14 IS NULL OR '' = RTRIM(@ParamName14)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,14
			,@ParamName14
			,@ParamData14
			)
	--- @ParamName15
	IF @ParamName15 IS NULL OR '' = RTRIM(@ParamName15)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,15
			,@ParamName15
			,@ParamData15
			)
	--- @ParamName16
	IF @ParamName16 IS NULL OR '' = RTRIM(@ParamName16)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,16
			,@ParamName16
			,@ParamData16
			)
	--- @ParamName17
	IF @ParamName17 IS NULL OR '' = RTRIM(@ParamName17)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,17
			,@ParamName17
			,@ParamData17
			)
	--- @ParamName18
	IF @ParamName18 IS NULL OR '' = RTRIM(@ParamName18)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,18
			,@ParamName18
			,@ParamData18
			)
	--- @ParamName19
	IF @ParamName19 IS NULL OR '' = RTRIM(@ParamName19)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,19
			,@ParamName19
			,@ParamData19
			)
	--- @ParamName20
	IF @ParamName20 IS NULL OR '' = RTRIM(@ParamName20)
		RETURN
	INSERT INTO [dbo].[DefTestParam]
			([DefTestStepID]
			,[SeqNo]
			,[ParamName]
			,[ParamData]
			)
		VALUES
			(@TestStepID
			,20
			,@ParamName20
			,@ParamData20
			)

	---SELECT @TestStepID
END
GO

/* 
	Finalizes the import and makes the test case active
*/
IF (object_id('dbo.PortalActivateTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalActivateTestCase
GO
CREATE PROCEDURE dbo.PortalActivateTestCase
(
        @TestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @OldTestCaseID int
	DECLARE @TestCaseName varchar(255)
	SELECT @TestCaseName = TestCaseName FROM dbo.DefTestCase WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID
	IF NOT EXISTS ( SELECT 1 FROM dbo.DefTestCase WITH (NOLOCK) WHERE TestCaseName = @TestCaseName AND Active=1 )
		SET @OldTestCaseID = NULL
	ELSE
	BEGIN
		SELECT @OldTestCaseID = DefTestCaseID FROM dbo.DefTestCase WITH (NOLOCK) WHERE TestCaseName = @TestCaseName AND Active=1
		--- Deactivate old test case
		UPDATE dbo.DefTestCase SET Active=0 WHERE DefTestCaseID = @OldTestCaseID
	END

	--- Activate new test case
	UPDATE dbo.DefTestCase SET Active=1 WHERE DefTestCaseID = @TestCaseID
	
	--- Update any existing test sets
	IF NOT (@OldTestCaseID IS NULL)
		UPDATE dbo.DefTestSetCases SET DefTestCaseID = @TestCaseID WHERE DefTestCaseID = @OldTestCaseID
		
END
GO

/* 
	Sets a test case inactive (effectively deleting it)
*/
IF (object_id('dbo.PortalDeleteTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDeleteTestCase
GO
CREATE PROCEDURE dbo.PortalDeleteTestCase
(
        @TestCaseID int,
		@UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Deactivate test case
	UPDATE dbo.DefTestCase
		SET 
			 Active=0
			,LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefTestCaseID = @TestCaseID

	--- Update any existing test sets
	DELETE FROM dbo.DefTestSetCases WHERE DefTestCaseID = @TestCaseID
		
END
GO

/* 
	Imports a test case from the import tables
*/
IF (object_id('dbo.PortalImportTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalImportTestCase
GO
CREATE PROCEDURE dbo.PortalImportTestCase
(
        @ImportTableID int
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Import ID
	IF NOT EXISTS ( SELECT 1 FROM dbo.TestCaseImport WITH (NOLOCK) WHERE uniqueID = @ImportTableID )
	BEGIN
		RAISERROR('Referenced ImportTableID does not exist in dbo.TestCaseImport', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	-- Check for valid Steps
	IF NOT EXISTS ( SELECT 1 FROM dbo.TestStep_line WITH (NOLOCK) WHERE step_lineID_foreignkey = @ImportTableID )
	BEGIN
		RAISERROR('Referenced ImportTableID does not exist in dbo.TestStep_line', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	-- Get Test Case Details
	DECLARE @lTestCaseName varchar(255)
	DECLARE @lQCID varchar(10)
	DECLARE @lMaxExecutionTimeMins int
	DECLARE @lDescription varchar(2048)
	DECLARE @lImportedBy varchar(255)
	DECLARE @lImportedDate datetime

	SELECT
			@lTestCaseName = [caseName],
			@lQCID = [caseID],
			@lDescription = [description],
			@lMaxExecutionTimeMins = 0, --TRY_PARSE([baseState] as int),
			@lImportedDate = [dateCreated],
			@lImportedBy = [createdBy]
		FROM dbo.TestCaseImport
			WITH (NOLOCK)
		WHERE [uniqueID] = @ImportTableID

	-- Build Step Table
	DECLARE @TestStepsTable TABLE (
		[title1] [varchar](max), [instruction1] [varchar](max),
		[title2] [varchar](max), [instruction2] [varchar](max),
		[title3] [varchar](max), [instruction3] [varchar](max),
		[title4] [varchar](max), [instruction4] [varchar](max),
		[title5] [varchar](max), [instruction5] [varchar](max),
		[title6] [varchar](max), [instruction6] [varchar](max),
		[title7] [varchar](max), [instruction7] [varchar](max),
		[title8] [varchar](max), [instruction8] [varchar](max),
		[title9] [varchar](max), [instruction9] [varchar](max),
		[title10] [varchar](max), [instruction10] [varchar](max),
		[title11] [varchar](max), [instruction11] [varchar](max),
		[title12] [varchar](max), [instruction12] [varchar](max),
		[title13] [varchar](max), [instruction13] [varchar](max),
		[title14] [varchar](max), [instruction14] [varchar](max),
		[title15] [varchar](max), [instruction15] [varchar](max),
		[title16] [varchar](max), [instruction16] [varchar](max),
		[title17] [varchar](max), [instruction17] [varchar](max),
		[title18] [varchar](max), [instruction18] [varchar](max),
		[title19] [varchar](max), [instruction19] [varchar](max),
		[table_id] [int]
	)
	
	INSERT INTO @TestStepsTable
		SELECT
			[title1], [instruction1],
			[title2], [instruction2],
			[title3], [instruction3],
			[title4], [instruction4],
			[title5], [instruction5],
			[title6], [instruction6],
			[title7], [instruction7],
			[title8], [instruction8],
			[title9], [instruction9],
			[title10], [instruction10],
			[title11], [instruction11],
			[title12], [instruction12],
			[title13], [instruction13],
			[title14], [instruction14],
			[title15], [instruction15],
			[title16], [instruction16],
			[title17], [instruction17],
			[title18], [instruction18],
			[title19], [instruction19],
			[table_id]
		FROM
			dbo.TestStep_line
			WITH (NOLOCK)
		WHERE
			step_lineID_foreignkey = @ImportTableID
			AND NOT ((LEFT([title1],1) = '<') OR (LEFT([instruction1],1) = '<'))

	IF EXISTS
		(SELECT 1
			FROM @TestStepsTable ts
			LEFT JOIN ActionWords aw ON (aw.ActionWordName = ts.instruction1 AND aw.Active=1)
			WHERE aw.ActionWordName IS NULL)
		BEGIN
			--- Invalid sequence!
			RAISERROR('One or more Action Words are invalid.', 18, 1)
			IF @@Error != 0 
				RETURN
		END
	
	DECLARE @lTestCaseID int
	EXEC PortalAddTestCase
        @TestCaseName = @lTestCaseName,
        @QCID = @lQCID,
		@MaxExecutionTimeMins = @lMaxExecutionTimeMins,
        @Description = @lDescription,
        @ImportedBy = @lImportedBy,
		@ImportedDate = @lImportedDate,
		@OutIdentity = @lTestCaseID OUTPUT

	-- Pre-declare step vars
	DECLARE @lStepName varchar(255), @lActionWordName varchar(255)
	DECLARE @lParamName01 varchar(255), @lParamData01 varchar(2048)
	DECLARE @lParamName02 varchar(255), @lParamData02 varchar(2048)
	DECLARE @lParamName03 varchar(255), @lParamData03 varchar(2048)
	DECLARE @lParamName04 varchar(255), @lParamData04 varchar(2048)
	DECLARE @lParamName05 varchar(255), @lParamData05 varchar(2048)
	DECLARE @lParamName06 varchar(255), @lParamData06 varchar(2048)
	DECLARE @lParamName07 varchar(255), @lParamData07 varchar(2048)
	DECLARE @lParamName08 varchar(255), @lParamData08 varchar(2048)
	DECLARE @lParamName09 varchar(255), @lParamData09 varchar(2048)
	DECLARE @lParamName10 varchar(255), @lParamData10 varchar(2048)
	DECLARE @lParamName11 varchar(255), @lParamData11 varchar(2048)
	DECLARE @lParamName12 varchar(255), @lParamData12 varchar(2048)
	DECLARE @lParamName13 varchar(255), @lParamData13 varchar(2048)
	DECLARE @lParamName14 varchar(255), @lParamData14 varchar(2048)
	DECLARE @lParamName15 varchar(255), @lParamData15 varchar(2048)
	DECLARE @lParamName16 varchar(255), @lParamData16 varchar(2048)
	DECLARE @lParamName17 varchar(255), @lParamData17 varchar(2048)
	DECLARE @lParamName18 varchar(255), @lParamData18 varchar(2048)
	DECLARE @lParamName19 varchar(255), @lParamData19 varchar(2048)
		
	-- Post the steps to the DB
	DECLARE @StepSeq int = 0

	-- Iterate through the steps
	WHILE (1 = 1) 
	BEGIN  
		-- Get next Step
		SELECT TOP 1 @StepSeq = ts.table_id
		FROM @TestStepsTable ts
		WHERE ts.table_id > @StepSeq
		ORDER BY ts.table_id

		-- Exit loop if no more customers
		IF @@ROWCOUNT = 0 BREAK;

		-- Get values from DB
		SELECT 
				 @lStepName = title1
				,@lActionWordName = instruction1
				,@lParamName01 = title2  , @lParamData01 = instruction2
				,@lParamName02 = title3  , @lParamData02 = instruction3
				,@lParamName03 = title4  , @lParamData03 = instruction4
				,@lParamName04 = title5  , @lParamData04 = instruction5
				,@lParamName05 = title6  , @lParamData05 = instruction6
				,@lParamName06 = title7  , @lParamData06 = instruction7
				,@lParamName07 = title8  , @lParamData07 = instruction8
				,@lParamName08 = title9  , @lParamData08 = instruction9
				,@lParamName09 = title10  , @lParamData09 = instruction10
				,@lParamName10 = title11  , @lParamData10 = instruction11
				,@lParamName11 = title12  , @lParamData11 = instruction12
				,@lParamName12 = title13  , @lParamData12 = instruction13
				,@lParamName13 = title14  , @lParamData13 = instruction14
				,@lParamName14 = title15  , @lParamData14 = instruction15
				,@lParamName15 = title16  , @lParamData15 = instruction16
				,@lParamName16 = title17  , @lParamData16 = instruction17
				,@lParamName17 = title18  , @lParamData17 = instruction18
				,@lParamName18 = title19  , @lParamData18 = instruction19
			FROM @TestStepsTable
			WHERE table_id = @StepSeq
		-- Add Step
		EXEC PortalAddTestStep
			@TestCaseID = @lTestCaseID,
			@StepName = @lStepName,
			@ActionWordName = @lActionWordName,
			@ParamName01 = @lParamName01, @ParamData01 = @lParamData01,
			@ParamName02 = @lParamName02, @ParamData02 = @lParamData02,
			@ParamName03 = @lParamName03, @ParamData03 = @lParamData03,
			@ParamName04 = @lParamName04, @ParamData04 = @lParamData04,
			@ParamName05 = @lParamName05, @ParamData05 = @lParamData05,
			@ParamName06 = @lParamName06, @ParamData06 = @lParamData06,
			@ParamName07 = @lParamName07, @ParamData07 = @lParamData07,
			@ParamName08 = @lParamName08, @ParamData08 = @lParamData08,
			@ParamName09 = @lParamName09, @ParamData09 = @lParamData09,
			@ParamName10 = @lParamName10, @ParamData10 = @lParamData10,
			@ParamName11 = @lParamName11, @ParamData11 = @lParamData11,
			@ParamName12 = @lParamName12, @ParamData12 = @lParamData12,
			@ParamName13 = @lParamName13, @ParamData13 = @lParamData13,
			@ParamName14 = @lParamName14, @ParamData14 = @lParamData14,
			@ParamName15 = @lParamName15, @ParamData15 = @lParamData15,
			@ParamName16 = @lParamName16, @ParamData16 = @lParamData16,
			@ParamName17 = @lParamName17, @ParamData17 = @lParamData17,
			@ParamName18 = @lParamName18, @ParamData18 = @lParamData18


	END
	
	-- Activate Test Case
	EXEC PortalActivateTestCase @TestCaseID = @lTestCaseID
END
GO

/* 
	Create new Import Table Test Case
*/
IF (object_id('dbo.PortalCreateImportTCRow')) IS NOT NULL
	DROP PROCEDURE dbo.PortalCreateImportTCRow
GO
CREATE PROCEDURE dbo.PortalCreateImportTCRow
(
        @Name varchar(255),
        @QCID varchar(255) = NULL,
        @Description varchar(2048) = NULL,
        @BaseState varchar(255) = NULL,
        @CreatedBy varchar(255) = NULL,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid TestSet Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('Test Case Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @QCID IS NOT NULL
		IF '' = RTRIM(@QCID)
			SET @QCID = NULL
	IF @BaseState IS NOT NULL
		IF '' = RTRIM(@BaseState)
			SET @BaseState = NULL
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL

	--- Insert
	INSERT INTO [dbo].[TestCaseImport]
			([caseName]
			,[caseID]
			,[description]
			,[baseState]
			,[createdBy]
			,[dateCreated]
			)
		VALUES
			(@Name
			,@QCID
			,@Description
			,@BaseState
			,@CreatedBy
			,GETDATE()
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	
END
GO

/* 
	Create new Import Table Test Case Step
*/
IF (object_id('dbo.PortalAddImportItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddImportItem
GO
CREATE PROCEDURE dbo.PortalAddImportItem
(
		@ID int,
		@T1 varchar(max) = NULL, @I1 varchar(MAX) = NULL,
		@T2 varchar(max) = NULL, @I2 varchar(MAX) = NULL,
		@T3 varchar(max) = NULL, @I3 varchar(MAX) = NULL,
		@T4 varchar(max) = NULL, @I4 varchar(MAX) = NULL,
		@T5 varchar(max) = NULL, @I5 varchar(MAX) = NULL,
		@T6 varchar(max) = NULL, @I6 varchar(MAX) = NULL,
		@T7 varchar(max) = NULL, @I7 varchar(MAX) = NULL,
		@T8 varchar(max) = NULL, @I8 varchar(MAX) = NULL,
		@T9 varchar(max) = NULL, @I9 varchar(MAX) = NULL,
		@T10 varchar(max) = NULL, @I10 varchar(MAX) = NULL,
		@T11 varchar(max) = NULL, @I11 varchar(MAX) = NULL,
		@T12 varchar(max) = NULL, @I12 varchar(MAX) = NULL,
		@T13 varchar(max) = NULL, @I13 varchar(MAX) = NULL,
		@T14 varchar(max) = NULL, @I14 varchar(MAX) = NULL,
		@T15 varchar(max) = NULL, @I15 varchar(MAX) = NULL,
		@T16 varchar(max) = NULL, @I16 varchar(MAX) = NULL,
		@T17 varchar(max) = NULL, @I17 varchar(MAX) = NULL,
		@T18 varchar(max) = NULL, @I18 varchar(MAX) = NULL,
		@T19 varchar(max) = NULL, @I19 varchar(MAX) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Insert
	INSERT INTO [dbo].[TestStep_line]
		VALUES
			(
			@ID,
			@T1, @I1,
			@T2, @I2,
			@T3, @I3,
			@T4, @I4,
			@T5, @I5,
			@T6, @I6,
			@T7, @I7,
			@T8, @I8,
			@T9, @I9,
			@T10, @I10,
			@T11, @I11,
			@T12, @I12,
			@T13, @I13,
			@T14, @I14,
			@T15, @I15,
			@T16, @I16,
			@T17, @I17,
			@T18, @I18,
			@T19, @I19
			)
END
GO


/* 
	Returns the full list of active test cases
*/
IF (object_id('dbo.PortalGetTestCaseList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestCaseList
GO
CREATE PROCEDURE dbo.PortalGetTestCaseList
(
        @TestCaseID int = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @TestCaseID IS NULL
		SELECT   deftc.[DefTestCaseID] as 'ID'
				,deftc.[TestCaseName] as 'Name'
				,deftc.[QCID] as 'QCID'
				,(SELECT COUNT(deftc2.DefTestCaseID)
					FROM DefTestCase deftc2 WITH (NOLOCK)
					WHERE deftc2.TestCaseName = deftc.TestCaseName
				 ) as 'Revision'
				,deftc.[Description] as 'Description'
				,(CASE ISNULL(deftc.[TargetSystem],0)
					WHEN 0 THEN 'NetSight Meter'
					ELSE
					deftc.[TargetSystem]
					END
				 ) as 'TargetSystem'
				,deftc.[BaseState] as 'Base State'
				,(ISNULL(deftc.[MaxExecutionTimeMins],-1)
				 ) as 'MaxExecutionTimeMins'
				,deftc.[LastUpdatedDate] as 'Imported Date'
				,deftc.[LastUpdatedBy] as 'Imported By'
			FROM [dbo].[DefTestCase] deftc
				WITH (NOLOCK)
			WHERE deftc.[Active]=1
			ORDER BY deftc.[TestCaseName]
		ELSE
		SELECT   deftc.[DefTestCaseID] as 'ID'
				,deftc.[TestCaseName] as 'Name'
				,deftc.[QCID] as 'QCID'
				,(SELECT COUNT(deftc2.DefTestCaseID)
					FROM DefTestCase deftc2 WITH (NOLOCK)
					WHERE deftc2.TestCaseName = deftc.TestCaseName
				 ) as 'Revision'
				,deftc.[Description] as 'Description'
				,(CASE ISNULL(deftc.[TargetSystem],0)
					WHEN 0 THEN 'NetSight Meter'
					ELSE
					deftc.[TargetSystem]
					END
				 ) as 'TargetSystem'
				,deftc.[BaseState] as 'Base State'
				,(ISNULL(deftc.[MaxExecutionTimeMins],-1)
				 ) as 'MaxExecutionTimeMins'
				,deftc.[LastUpdatedDate] as 'Imported Date'
				,deftc.[LastUpdatedBy] as 'Imported By'
			FROM [dbo].[DefTestCase] deftc
				WITH (NOLOCK)
			WHERE deftc.[Active]=1 AND deftc.[DefTestCaseID] = @TestCaseID
END
GO

/* 
	Returns the full list of active test cases with count of steps
*/
IF (object_id('dbo.PortalGetTestCaseListEx')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestCaseListEx
GO
CREATE PROCEDURE dbo.PortalGetTestCaseListEx
AS
BEGIN
	SET NOCOUNT ON

	SELECT   p.DefTestCaseID as 'ID'
			,p.TestCaseName as 'Name'
			,p.QCID as 'QCID'
			,p.Description as 'Description'
			,(SELECT COUNT(deftc2.DefTestCaseID)
				FROM DefTestCase deftc2 WITH (NOLOCK)
				WHERE deftc2.TestCaseName = p.TestCaseName
				) as 'Revision'
			,(CASE ISNULL(p.[TargetSystem],0)
				WHEN 0 THEN 'NetSight Meter'
				ELSE
				p.[TargetSystem]
				END
				) as 'TargetSystem'
			,p.BaseState as 'Base State'
			,(ISNULL([MaxExecutionTimeMins],-1)
				) as 'MaxExecutionTimeMins'
			,p.LastUpdatedDate as 'Imported Date'
			,p.LastUpdatedBy as 'Imported By'
			,(SELECT COUNT(c.DefTestStepID)
				FROM [dbo].[DefTestStep] c
				WITH (NOLOCK)
				WHERE c.DefTestCaseID = p.DefTestCaseID
			 ) as 'Steps Count'
		FROM [dbo].[DefTestCase] p
			WITH (NOLOCK)
		WHERE p.Active=1
		ORDER BY p.TestCaseName
END
GO

/* 
	Returns the step details of an individual test case
	In a columnar format similar to the spreadsheet template
*/
IF (object_id('dbo.PortalGetTestCaseSteps')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestCaseSteps
GO
CREATE PROCEDURE dbo.PortalGetTestCaseSteps
(
        @TestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TestStepsResults TABLE
	(
		lCol01 varchar(2048) NULL,
		lCol02 varchar(2048) NULL,
		lCol03 varchar(2048) NULL,
		lCol04 varchar(2048) NULL,
		lCol05 varchar(2048) NULL,
		lCol06 varchar(2048) NULL,
		lCol07 varchar(2048) NULL,
		lCol08 varchar(2048) NULL,
		lCol09 varchar(2048) NULL,
		lCol10 varchar(2048) NULL,
		lCol11 varchar(2048) NULL,
		lCol12 varchar(2048) NULL,
		lCol13 varchar(2048) NULL,
		lCol14 varchar(2048) NULL,
		lCol15 varchar(2048) NULL,
		lCol16 varchar(2048) NULL,
		lCol17 varchar(2048) NULL,
		lCol18 varchar(2048) NULL,
		lCol19 varchar(2048) NULL,
		lCol20 varchar(2048) NULL
	)
	DECLARE @DefTestStepTmp TABLE
	(
		DefTestStepID [int] NOT NULL,
		StepName [varchar](255) NOT NULL,
		ActionWordName [varchar](255) NOT NULL,
		stepSeqNo [int] NOT NULL
	)
	DECLARE @DefTestParamTmp TABLE
	(
		paramDefTestStepID [int] NOT NULL,
		paramSeqNo [int] NOT NULL,
		ParamName [varchar](255) NOT NULL,
		ParamData [varchar](2048) NOT NULL
	)

	--- Get the steps
	INSERT INTO @DefTestStepTmp
		SELECT
			p.DefTestStepID,
			p.StepName,
			(SELECT c.ActionWordName FROM ActionWords c WITH (NOLOCK) WHERE c.ActionWordID = p.ActionWordID),
			p.SeqNo
		FROM DefTestStep p
			WITH (NOLOCK)
		WHERE p.DefTestCaseID = @TestCaseID
		ORDER BY p.SeqNo

	--- Get the parameters
	INSERT INTO @DefTestParamTmp
		SELECT
			p.DefTestStepID,
			p.SeqNo,
			p.ParamName,
			p.ParamData
		FROM DefTestParam p
			WITH (NOLOCK)
		WHERE p.DefTestStepID IN (SELECT DefTestStepID FROM @DefTestStepTmp)

	-- Build the table
	DECLARE @StepSeq int = 0, @StepID int
	DECLARE @pActionWordName varchar(255), @pStepName varchar(255)
	-- Iterate through the steps
	WHILE (1 = 1) 
	BEGIN  
		-- Get next Step
		SELECT TOP 1	 @StepSeq = ts.stepSeqNo
						,@StepID = ts.DefTestStepID
						,@pActionWordName = ts.ActionWordName
						,@pStepName = ts.StepName
		FROM @DefTestStepTmp ts
		WHERE ts.stepSeqNo > @StepSeq
		ORDER BY ts.stepSeqNo

		-- Exit loop if no more steps
		IF @@ROWCOUNT = 0 BREAK;

		--- first row is step name and then param headers
		INSERT INTO @TestStepsResults
		VALUES
		(
			 @pStepName
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 1)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 2)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 3)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 4)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 5)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 6)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 7)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 8)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 9)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 10)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 11)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 12)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 13)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 14)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 15)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 16)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 17)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 18)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 19)
		)
		--- second row is action word name and then param data
		INSERT INTO @TestStepsResults
		VALUES
		(
			 @pActionWordName
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 1)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 2)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 3)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 4)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 5)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 6)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 7)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 8)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 9)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 10)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 11)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 12)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 13)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 14)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 15)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 16)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 17)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 18)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 19)
		)
	END

	--- Return the results
	SELECT * FROM @TestStepsResults
END
GO

/* 
	Simply creates a new Test Set record and returns the identity
*/
IF (object_id('dbo.PortalAddTestSet')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestSet
GO
CREATE PROCEDURE dbo.PortalAddTestSet
(
        @TestSetName varchar(255),
		@ExecutionOrder int,
		@MachineOp int,
        @Description varchar(1024) = NULL,
        @CreatedBy varchar(255) = NULL,
		@FolderID int = NULL,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid TestSet Name
	IF '' = RTRIM(@TestSetName)
	BEGIN
		RAISERROR('Test Set Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	--- Check for duplicate TestSet Name
	IF EXISTS (SELECT 1 FROM dbo.DefTestSet WITH (NOLOCK) WHERE TestSetName = @TestSetName AND Active=1)
	BEGIN
		RAISERROR('Test Set Name already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	IF @FolderID IS NULL
		EXEC GetRootFolderID @ObjectType = 'DefTestSet', @OutIdentity = @FolderID OUTPUT

	--- Insert
	INSERT INTO [dbo].[DefTestSet]
			([DefFolderID]
			,[TestSetName]
			,[ExecutionOrder]
			,[MachineOp]
			,[Description]
			,[Active]
			,[LastUpdatedDate]
			,[LastUpdatedBy]
			)
		VALUES
			(@FolderID
			,@TestSetName
			,@ExecutionOrder
			,@MachineOp
			,@Description
			,1
			,GETDATE()
			,@CreatedBy
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Simply creates a new Test Set record and returns the identity
*/
IF (object_id('dbo.PortalUpdateTestSet')) IS NOT NULL
	DROP PROCEDURE dbo.PortalUpdateTestSet
GO
CREATE PROCEDURE dbo.PortalUpdateTestSet
(
		@DefTestSetID int,
        @TestSetName varchar(255),
		@ExecutionOrder int,
		@MachineOp int,
        @Description varchar(1024) = NULL,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid TestSet Name
	IF '' = RTRIM(@TestSetName)
	BEGIN
		RAISERROR('Test Set Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	--- Check for duplicate TestSet Name
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestSet WITH (NOLOCK) 
					WHERE DefTestSetID = @DefTestSetID
						AND Active=1
				  )
	BEGIN
		RAISERROR('Test Set ID provided does not exist.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END
	IF EXISTS (SELECT 1 FROM dbo.DefTestSet WITH (NOLOCK) 
				WHERE TestSetName = @TestSetName
					AND Active=1 
					AND DefTestSetID <> @DefTestSetID)
	BEGIN
		RAISERROR('Test Set Name already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL

	--- Insert
	UPDATE [dbo].[DefTestSet]
		SET
			 [TestSetName] = @TestSetName
			,[ExecutionOrder] = @ExecutionOrder
			,[MachineOp] = @MachineOp
			,[Description] = @Description
			,[LastUpdatedBy] = @UpdatedBy
			,[LastUpdatedDate] = GETDATE()
		WHERE DefTestSetID = @DefTestSetID
END
GO


/* 
	Adds a Test Case record to an existing Test Set by ID
*/
IF (object_id('dbo.PortalAssignTestSetTestCaseByID')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAssignTestSetTestCaseByID
GO
CREATE PROCEDURE dbo.PortalAssignTestSetTestCaseByID
(
        @TestSetID int,
		@TestCaseID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Test Set ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestSet WITH (NOLOCK) WHERE DefTestSetID = @TestSetID AND Active=1)
	BEGIN
		RAISERROR('Invalid @TestSetID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	--- Check for valid Test Case ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestCase WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID AND Active=1)
	BEGIN
		RAISERROR('Invalid @TestCaseID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	--- Check if already added
	IF EXISTS (SELECT 1 FROM dbo.DefTestSetCases WITH (NOLOCK)
				WHERE DefTestSetID = @TestSetID AND DefTestCaseID = @TestCaseID)
		RETURN(0)

	DECLARE @Order float = null
	SELECT @Order = [Order]
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE [Order] = (SELECT MAX([Order]) FROM dbo.DefTestSetCases WITH (NOLOCK)
							WHERE DefTestSetID = @TestSetID)
							
	IF @Order IS NULL
		SET @Order = 100
	ELSE
		SET @Order = @Order + 100
		 
	--- Add the test case to the test set
	INSERT INTO dbo.defTestSetCases
	(
		 [DefTestSetID]
		,[DefTestCaseID]
		,[Order]
	)
	VALUES
	(
		 @TestSetID
		,@TestCaseID
		,@Order
	)
	
	--- Revise last update fields
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.DefTestSet
		SET 
			 LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefTestSetID = @TestSetID
	
	RETURN(0)
END
GO

/* 
	Adds/removes Test Data Set assignment by ID
*/
IF (object_id('dbo.PortalAssignDataSetForTestSetTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAssignDataSetForTestSetTestCase
GO
CREATE PROCEDURE dbo.PortalAssignDataSetForTestSetTestCase
(
        @TestSetCasesID int,
		@TestDataSetID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Add the test case to the test set
	UPDATE dbo.DefTestSetCases
		SET [DefTestDataSetID] = @TestDataSetID
		WHERE DefTestSetCasesID = @TestSetCasesID
	
	RETURN(0)
END
GO

/* 
	Adds a Test Case record to an existing Test Set by Name
*/
IF (object_id('dbo.PortalAssignTestSetTestCaseByName')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAssignTestSetTestCaseByName
GO
CREATE PROCEDURE dbo.PortalAssignTestSetTestCaseByName
(
        @TestSetName varchar(255),
		@TestCaseName varchar(255),
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @pTestSetID int = NULL, @pTestCaseID int = NULL

	SELECT @pTestSetID = DefTestSetID FROM dbo.DefTestSet WITH (NOLOCK) WHERE TestSetName = @TestSetName AND Active=1
	IF @pTestSetID IS NULL
	BEGIN
		RAISERROR('Invalid @TestSetName.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

	SELECT @pTestCaseID = DefTestCaseID FROM dbo.DefTestCase WITH (NOLOCK) WHERE TestCaseName = @TestCaseName AND Active=1
	IF @pTestCaseID IS NULL
	BEGIN
		RAISERROR('Invalid @TestCaseID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	
	EXEC PortalAssignTestSetTestCaseByID
		 @TestSetID = @pTestSetID
		,@TestCaseID = @pTestCaseID
		,@UpdatedBy = @UpdatedBy
	
	RETURN(0)
END
GO

/* 
	Removes a Test Case record from an existing Test Set
*/
IF (object_id('dbo.PortalRemoveTestSetTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalRemoveTestSetTestCase
GO
CREATE PROCEDURE dbo.PortalRemoveTestSetTestCase
(
        @TestSetID int,
		@TestCaseID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Test Set ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestSet WITH (NOLOCK) WHERE DefTestSetID = @TestSetID AND Active=1)
	BEGIN
		RAISERROR('Invalid @TestSetID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	--- Check for valid Test Case ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestCase WITH (NOLOCK) WHERE DefTestCaseID = @TestCaseID AND Active=1)
	BEGIN
		RAISERROR('Invalid @TestCaseID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	--- Check if already present and remove
	DELETE FROM dbo.DefTestSetCases WHERE DefTestSetID = @TestSetID AND DefTestCaseID = @TestCaseID

	--- Revise last update fields
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.DefTestSet
		SET 
			 LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefTestSetID = @TestSetID
		
	RETURN(0)
END
GO


/* 
	Moves a test case assignment up in order
*/
IF (object_id('dbo.PortalTestSetTestCaseUpLevel')) IS NOT NULL
	DROP PROCEDURE dbo.PortalTestSetTestCaseUpLevel
GO
CREATE PROCEDURE dbo.PortalTestSetTestCaseUpLevel
(
		@TestSetCasesID int
)
AS
BEGIN
	SET NOCOUNT ON


	DECLARE @TestSetID int
	DECLARE @LevelMe float
	DECLARE @LevelMin float
	DECLARE @LevelMax float
	SELECT @LevelMe = [Order]
	      ,@TestSetID = DefTestSetID
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE DefTestSetCasesID = @TestSetCasesID
	SELECT @LevelMin = MIN([Order])
		  ,@LevelMax = MAX([Order])
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE DefTestSetID = @TestSetID

	--- Check if already at the top
	IF @LevelMe = @LevelMin
		RETURN(0)

	DECLARE @LevelAboveMe float
	SELECT TOP 1 @LevelAboveMe = [Order]
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE [Order] < @LevelMe
			AND DefTestSetID = @TestSetID
		ORDER BY [Order] DESC

	DECLARE @LevelAboveMe2 float = 0
	IF NOT @LevelAboveMe = @LevelMin
		SELECT TOP 1 @LevelAboveMe2 = [Order]
			FROM dbo.DefTestSetCases WITH (NOLOCK)
			WHERE [Order] < @LevelAboveMe
				AND DefTestSetID = @TestSetID
			ORDER BY [Order] DESC

	-- calculate new level
	SET @LevelMe = @LevelAboveMe2 + ((@LevelAboveMe - @LevelAboveMe2)/2)
	-- store to DB
	UPDATE dbo.DefTestSetCases
		SET [Order] = @LevelMe
		WHERE DefTestSetCasesID = @TestSetCasesID		

	RETURN(0)
END
GO

/* 
	Moves a test case assignment down in order
*/
IF (object_id('dbo.PortalTestSetTestCaseDownLevel')) IS NOT NULL
	DROP PROCEDURE dbo.PortalTestSetTestCaseDownLevel
GO
CREATE PROCEDURE dbo.PortalTestSetTestCaseDownLevel
(
		@TestSetCasesID int
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TestSetID int
	DECLARE @LevelMe float
	DECLARE @LevelMin float
	DECLARE @LevelMax float
	SELECT @LevelMe = [Order]
	      ,@TestSetID = DefTestSetID
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE DefTestSetCasesID = @TestSetCasesID
	SELECT @LevelMin = MIN([Order])
		  ,@LevelMax = MAX([Order])
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE DefTestSetID = @TestSetID

	--- Check if already at the bottom
	IF @LevelMe = @LevelMax
		RETURN(0)

	DECLARE @LevelBelowMe float
	SELECT TOP 1 @LevelBelowMe = [Order]
		FROM dbo.DefTestSetCases WITH (NOLOCK)
		WHERE [Order] > @LevelMe
			AND DefTestSetID = @TestSetID
		ORDER BY [Order] ASC

	DECLARE @LevelBelowMe2 float = @LevelMax + 100
	IF NOT @LevelBelowMe = @LevelMax
		SELECT TOP 1 @LevelBelowMe2 = [Order]
			FROM dbo.DefTestSetCases WITH (NOLOCK)
			WHERE [Order] > @LevelBelowMe
				AND DefTestSetID = @TestSetID
			ORDER BY [Order] ASC

	-- calculate new level
	SET @LevelMe = @LevelBelowMe + ((@LevelBelowMe2 - @LevelBelowMe)/2)

	-- store to DB
	UPDATE dbo.DefTestSetCases
		SET [Order] = @LevelMe
		WHERE DefTestSetCasesID = @TestSetCasesID		

	RETURN(0)
END
GO

/* 
	Returns list of Test Sets
*/
IF (object_id('dbo.PortalGetTestSetList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestSetList
GO
CREATE PROCEDURE dbo.PortalGetTestSetList
(
        @TestSetID int = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @TestSetID IS NULL
		SELECT
			 p.[DefTestSetID] as 'ID'
			,p.[TestSetName] as 'Name'
			,(SELECT ListItemValue FROM DefListItems WITH (NOLOCK)
				WHERE p.[ExecutionOrder] = DefListItems.DefListItemsID
			 ) as 'ExecutionOrder'
			,(SELECT ListItemValue FROM DefListItems WITH (NOLOCK)
				WHERE p.[MachineOp] = DefListItems.DefListItemsID
			 ) as 'MachineOp'
			,p.[Description] as 'Description'
			,p.[LastUpdatedDate] as 'Last Updated'
			,p.[LastUpdatedBy] as 'Last Updated By'
			,(SELECT COUNT(DefTestSetCasesID) FROM DefTestSetCases c WHERE c.DefTestSetID = p.DefTestSetID)
				as 'Test Case Count'
		FROM dbo.DefTestSet p WITH (NOLOCK)
		WHERE p.[Active]=1
		ORDER BY p.[TestSetName]
	ELSE
		SELECT
			 p.[DefTestSetID] as 'ID'
			,p.[TestSetName] as 'Name'
			,(SELECT ListItemValue FROM DefListItems WITH (NOLOCK)
				WHERE p.[ExecutionOrder] = DefListItems.DefListItemsID
			 ) as 'ExecutionOrder'
			,(SELECT ListItemValue FROM DefListItems WITH (NOLOCK)
				WHERE p.[MachineOp] = DefListItems.DefListItemsID
			 ) as 'MachineOp'
			,p.[Description] as 'Description'
			,p.[LastUpdatedDate] as 'Last Updated'
			,p.[LastUpdatedBy] as 'Last Updated By'
			,(SELECT COUNT(DefTestSetCasesID) FROM DefTestSetCases c WHERE c.DefTestSetID = p.DefTestSetID)
				as 'Test Case Count'
		FROM dbo.DefTestSet p WITH (NOLOCK)
		WHERE p.[Active]=1 AND p.[DefTestSetID] = @TestSetID
	
END
GO

/* 
	Returns individual Test Set for Edit
*/
IF (object_id('dbo.PortalGetTestSetEdit')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestSetEdit
GO
CREATE PROCEDURE dbo.PortalGetTestSetEdit
(
        @TestSetID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 [TestSetName] as 'Name'
		,[ExecutionOrder] as 'ExecutionOrder'
		,[MachineOp] as 'MachineOp'
		,[Description] as 'Description'
	FROM dbo.DefTestSet WITH (NOLOCK)
	WHERE [DefTestSetID] = @TestSetID
	
END
GO

/* 
	Returns Test Set valid values for [ExecutionOrder] column
*/
IF (object_id('dbo.PortalGetTestColExecutionOrderValuesList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestColExecutionOrderValuesList
GO
CREATE PROCEDURE dbo.PortalGetTestColExecutionOrderValuesList
AS
BEGIN
	SET NOCOUNT ON

    SELECT
		 DefListItemsID as 'ID'
		,ListItemValue as 'Value'
	FROM dbo.DefListItems WITH (NOLOCK)
	WHERE DefListItems.DefListID = (SELECT DefList.DefListID
									FROM DefList WITH (NOLOCK)
									WHERE DefList.ListName = 'DefTestExecutionOrder'
								   )
	ORDER BY ListItemValue
	
END
GO

/* 
	Returns Test Set valid values for [MachineOp] column
*/
IF (object_id('dbo.PortalGetTestColMachineOpValuesList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestColMachineOpValuesList
GO
CREATE PROCEDURE dbo.PortalGetTestColMachineOpValuesList
AS
BEGIN
	SET NOCOUNT ON

    SELECT
		 DefListItemsID as 'ID'
		,ListItemValue as 'Value'
	FROM dbo.DefListItems WITH (NOLOCK)
	WHERE DefListItems.DefListID = (SELECT DefList.DefListID
									FROM DefList WITH (NOLOCK)
									WHERE DefList.ListName = 'DefTestMachineOp'
								   )
	ORDER BY ListItemValue
	
END
GO

/* 
	Returns the full list of test cases assigned to a test set
*/
IF (object_id('dbo.PortalGetTestSetTestCaseList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestSetTestCaseList
GO
CREATE PROCEDURE dbo.PortalGetTestSetTestCaseList
(
        @TestSetID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT   [DefTestSetCases].[DefTestSetCasesID] as 'ID'
			,deftc.[DefTestCaseID] as 'Test Case ID'
			,deftc.[TestCaseName] as 'Name'
			,deftc.[QCID] as 'QCID'
			,(SELECT COUNT(deftc2.DefTestCaseID)
				FROM DefTestCase deftc2 WITH (NOLOCK)
				WHERE deftc2.TestCaseName = deftc.TestCaseName
				) as 'Revision'
			,(SELECT ISNULL(DefTestDataSet.TestDataSetName, 'None') 
				FROM DefTestDataSet WITH (NOLOCK)
				WHERE DefTestDataSet.DefTestDataSetID = DefTestSetCases.DefTestDataSetID
			 ) as 'Test Data Set'
			,deftc.[Description] as 'Description'
			,deftc.[LastUpdatedDate] as 'Imported Date'
			,deftc.[LastUpdatedBy] as 'Imported By'
		FROM [dbo].[DefTestCase] deftc WITH (NOLOCK)
		LEFT JOIN [dbo].[DefTestSetCases] WITH (NOLOCK)
				ON [DefTestSetCases].[DefTestCaseID] = deftc.[DefTestCaseID]
		WHERE  [DefTestSetCases].[DefTestSetID] = @TestSetID
		ORDER BY [DefTestSetCases].[Order]
END
GO

/* 
	Returns the full list of test cases with an attribute denoting if added or not
*/
IF (object_id('dbo.PortalGetTestSetTestCaseAssignments')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestSetTestCaseAssignments
GO
CREATE PROCEDURE dbo.PortalGetTestSetTestCaseAssignments
(
        @TestSetID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT   (SELECT 'Yes' 
				FROM [dbo].[DefTestSetCases] c WITH (NOLOCK)
				WHERE c.[DefTestSetID] = @TestSetID AND c.[DefTestCaseID] = p.[DefTestCaseID]
			 ) as 'Member'
			,p.[DefTestCaseID] as 'ID'
			,p.[TestCaseName] as 'Name'
			,p.[QCID] as 'QCID'
			,(SELECT COUNT(deftc2.DefTestCaseID)
				FROM DefTestCase deftc2 WITH (NOLOCK)
				WHERE deftc2.TestCaseName = p.TestCaseName
				) as 'Revision'
			,p.[Description] as 'Description'
			,p.[LastUpdatedDate] as 'Imported Date'
			,p.[LastUpdatedBy] as 'Imported By'
			FROM [dbo].[DefTestCase] p
			WITH (NOLOCK)
			WHERE p.[Active]=1
		ORDER BY p.[TestCaseName]
END
GO

/* 
	Retrieves Test Data Set assignment by ID
*/
IF (object_id('dbo.PortalGetTestSetTestCaseDataSet')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestSetTestCaseDataSet
GO
CREATE PROCEDURE dbo.PortalGetTestSetTestCaseDataSet
(
        @TestSetCasesID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		DefTestDataSetID as 'DefTestDataSetID'
	FROM dbo.DefTestSetCases WITH (NOLOCK)
	WHERE DefTestSetCasesID = @TestSetCasesID
	
	RETURN(0)
END
GO


/* 
	Create new Test Data Master Record
*/
IF (object_id('dbo.PortalAddTestData')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestData
GO
CREATE PROCEDURE dbo.PortalAddTestData
(
        @Name varchar(255),
        @Description varchar(255) = NULL,
		@DataPointName01 [varchar](255),
		@DataPointName02 [varchar](255) = NULL,
		@DataPointName03 [varchar](255) = NULL,
		@DataPointName04 [varchar](255) = NULL,
		@DataPointName05 [varchar](255) = NULL,
		@DataPointName06 [varchar](255) = NULL,
		@DataPointName07 [varchar](255) = NULL,
		@DataPointName08 [varchar](255) = NULL,
		@DataPointName09 [varchar](255) = NULL,
        @CreatedBy varchar(255) = NULL,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid TestSet Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('Test Set @Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END
	IF '' = RTRIM(@DataPointName01)
	BEGIN
		RAISERROR('@DataPointName01 cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @DataPointName02 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName02)
			SET @DataPointName02 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName02)
			SET @DataPointName02 = NULL
	END
	IF @DataPointName03 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName03)
			SET @DataPointName03 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName03)
			SET @DataPointName03 = NULL
	END
	IF @DataPointName04 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName04)
			SET @DataPointName04 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName04)
			SET @DataPointName04 = NULL
	END
	IF @DataPointName05 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName05)
			SET @DataPointName05 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName05)
			SET @DataPointName05 = NULL
	END
	IF @DataPointName06 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName06)
			SET @DataPointName06 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName06)
			SET @DataPointName06 = NULL
	END
	IF @DataPointName07 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName07)
			SET @DataPointName07 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName07)
			SET @DataPointName07 = NULL
	END
	IF @DataPointName08 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName08)
			SET @DataPointName08 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName08)
			SET @DataPointName08 = NULL
	END
	IF @DataPointName09 IS NOT NULL
	BEGIN
		IF '' = RTRIM(@DataPointName09)
			SET @DataPointName09 = NULL
		IF '<ValueName>' = RTRIM(@DataPointName09)
			SET @DataPointName09 = NULL
	END

	DECLARE @FolderID int
	EXEC GetRootFolderID @ObjectType = 'DefTestDataSet', @OutIdentity = @FolderID OUTPUT
	
	--- Insert
	INSERT INTO [dbo].[DefTestDataSet]
			([DefFolderID]
			,[TestDataSetName]
			,[Description]
			,[Active]
			,[LastUpdatedDate]
			,[LastUpdatedBy]
			,[DataPointName01]
			,[DataPointName02]
			,[DataPointName03]
			,[DataPointName04]
			,[DataPointName05]
			,[DataPointName06]
			,[DataPointName07]
			,[DataPointName08]
			,[DataPointName09]
			)
		VALUES
			(@FolderID
			,@Name
			,@Description
			,0
			,GETDATE()
			,@CreatedBy
			,@DataPointName01
			,@DataPointName02
			,@DataPointName03
			,@DataPointName04
			,@DataPointName05
			,@DataPointName06
			,@DataPointName07
			,@DataPointName08
			,@DataPointName09
			)

	SET @OutIdentity = SCOPE_IDENTITY()

END
GO

/* 
	Create new Test Data Item Record
*/
IF (object_id('dbo.PortalAddTestDataItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestDataItem
GO
CREATE PROCEDURE dbo.PortalAddTestDataItem
(
        @ID int,
		@SeqNo int = 0,
		@DataPointData01 [varchar](max),
		@DataPointData02 [varchar](max) = NULL,
		@DataPointData03 [varchar](max) = NULL,
		@DataPointData04 [varchar](max) = NULL,
		@DataPointData05 [varchar](max) = NULL,
		@DataPointData06 [varchar](max) = NULL,
		@DataPointData07 [varchar](max) = NULL,
		@DataPointData08 [varchar](max) = NULL,
		@DataPointData09 [varchar](max) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF '' = RTRIM(@DataPointData01)
	BEGIN
		RAISERROR('@DataPointData01 cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @DataPointData02 IS NOT NULL
		IF '' = RTRIM(@DataPointData02)
			SET @DataPointData02 = NULL
	IF @DataPointData03 IS NOT NULL
		IF '' = RTRIM(@DataPointData03)
			SET @DataPointData03 = NULL
	IF @DataPointData04 IS NOT NULL
		IF '' = RTRIM(@DataPointData04)
			SET @DataPointData04 = NULL
	IF @DataPointData05 IS NOT NULL
		IF '' = RTRIM(@DataPointData05)
			SET @DataPointData05 = NULL
	IF @DataPointData06 IS NOT NULL
		IF '' = RTRIM(@DataPointData06)
			SET @DataPointData06 = NULL
	IF @DataPointData07 IS NOT NULL
		IF '' = RTRIM(@DataPointData07)
			SET @DataPointData07 = NULL
	IF @DataPointData08 IS NOT NULL
		IF '' = RTRIM(@DataPointData08)
			SET @DataPointData08 = NULL
	IF @DataPointData09 IS NOT NULL
		IF '' = RTRIM(@DataPointData09)
			SET @DataPointData09 = NULL

	--- Insert
	INSERT INTO [dbo].[DefTestDataSetData]
			([DefTestDataSetID]
			,[SeqNo]
			,[DataPointData01]
			,[DataPointData02]
			,[DataPointData03]
			,[DataPointData04]
			,[DataPointData05]
			,[DataPointData06]
			,[DataPointData07]
			,[DataPointData08]
			,[DataPointData09]
			)
		VALUES
			(@ID
			,@SeqNo
			,@DataPointData01
			,@DataPointData02
			,@DataPointData03
			,@DataPointData04
			,@DataPointData05
			,@DataPointData06
			,@DataPointData07
			,@DataPointData08
			,@DataPointData09
			)

END
GO

/* 
	Activate Test Data Master Record
*/
IF (object_id('dbo.PortalActivateTestData')) IS NOT NULL
	DROP PROCEDURE dbo.PortalActivateTestData
GO
CREATE PROCEDURE dbo.PortalActivateTestData
(
        @ID int
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @OldTestDataSetID int
	DECLARE @TestDataSetName varchar(255)
	SELECT @TestDataSetName = TestDataSetName FROM dbo.DefTestDataSet WITH (NOLOCK) WHERE DefTestDataSetID = @ID
	IF NOT EXISTS ( SELECT 1 FROM dbo.DefTestDataSet WITH (NOLOCK) WHERE TestDataSetName = @TestDataSetName AND Active=1 )
		SET @OldTestDataSetID = NULL
	ELSE
	BEGIN
		SELECT @OldTestDataSetID = DefTestDataSetID FROM dbo.DefTestDataSet WITH (NOLOCK) WHERE TestDataSetName = @TestDataSetName AND Active=1
		--- Deactivate old test set
		UPDATE dbo.DefTestDataSet SET Active=0 WHERE DefTestDataSetID = @OldTestDataSetID
	END

	--- Activate new test case
	UPDATE dbo.DefTestDataSet SET Active=1 WHERE DefTestDataSetID = @ID
	
	--- Update any existing test set assignments
	IF NOT (@OldTestDataSetID IS NULL)
		UPDATE dbo.DefTestSetCases SET DefTestDataSetID = @ID WHERE DefTestDataSetID = @OldTestDataSetID

END
GO

/* 
	Returns the full list of active test data sets with count of value rows
*/
IF (object_id('dbo.PortalGetTestDataList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestDataList
GO
CREATE PROCEDURE dbo.PortalGetTestDataList
(
        @ID int = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @ID IS NULL
	BEGIN
		SELECT   p.DefTestDataSetID as 'ID'
				,p.TestDataSetName as 'Name'
				,(SELECT COUNT(deftd2.DefTestDataSetID)
					FROM DefTestDataSet deftd2 WITH (NOLOCK)
					WHERE deftd2.TestDataSetName = p.TestDataSetName
					) as 'Revision'
				,p.Description as 'Description'
				,p.LastUpdatedDate as 'Imported Date'
				,p.LastUpdatedBy as 'Imported By'
				,(SELECT COUNT(c.DefTestDataSetDataID)
					FROM [dbo].[DefTestDataSetData] c
					WITH (NOLOCK)
					WHERE c.DefTestDataSetID = p.DefTestDataSetID
				 ) as 'Value Rows'
			FROM [dbo].[DefTestDataSet] p
				WITH (NOLOCK)
			WHERE p.Active=1
			ORDER BY p.TestDataSetName
	END
	ELSE
	BEGIN
		SELECT   p.DefTestDataSetID as 'ID'
				,p.TestDataSetName as 'Name'
				,(SELECT COUNT(deftd2.DefTestDataSetID)
					FROM DefTestDataSet deftd2 WITH (NOLOCK)
					WHERE deftd2.TestDataSetName = p.TestDataSetName
					) as 'Revision'
				,p.Description as 'Description'
				,p.LastUpdatedDate as 'Imported Date'
				,p.LastUpdatedBy as 'Imported By'
				,(SELECT COUNT(c.DefTestDataSetDataID)
					FROM [dbo].[DefTestDataSetData] c
					WITH (NOLOCK)
					WHERE c.DefTestDataSetID = p.DefTestDataSetID
				 ) as 'Value Rows'
			FROM [dbo].[DefTestDataSet] p
				WITH (NOLOCK)
			WHERE p.DefTestDataSetID = @ID
			ORDER BY p.TestDataSetName
	END
END
GO

/* 
	Returns the details of a test data set
*/
IF (object_id('dbo.PortalGetTestDataDetail')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestDataDetail
GO
CREATE PROCEDURE dbo.PortalGetTestDataDetail
(
        @ID int
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @DataPointName01 [varchar](255)
	DECLARE @DataPointName02 [varchar](255)
	DECLARE @DataPointName03 [varchar](255)
	DECLARE @DataPointName04 [varchar](255)
	DECLARE @DataPointName05 [varchar](255)
	DECLARE @DataPointName06 [varchar](255)
	DECLARE @DataPointName07 [varchar](255)
	DECLARE @DataPointName08 [varchar](255)
	DECLARE @DataPointName09 [varchar](255)

	SELECT
			 @DataPointName01 = [DataPointName01]
			,@DataPointName02 = [DataPointName02]
			,@DataPointName03 = [DataPointName03]
			,@DataPointName04 = [DataPointName04]
			,@DataPointName05 = [DataPointName05]
			,@DataPointName06 = [DataPointName06]
			,@DataPointName07 = [DataPointName07]
			,@DataPointName08 = [DataPointName08]
			,@DataPointName09 = [DataPointName09]
		FROM [dbo].[DefTestDataSet]
			WITH (NOLOCK)
		WHERE DefTestDataSetID = @ID
	
	DECLARE @SQLStmt varchar(max) = 'SELECT '
	SET @SQLStmt = @SQLStmt + '[DataPointData01] AS [' + @DataPointName01 + ']'
	IF @DataPointName02 IS NOT NULL
	BEGIN
		SET @SQLStmt = @SQLStmt + ',[DataPointData02] AS [' + @DataPointName02 + ']'

		IF @DataPointName03 IS NOT NULL
		BEGIN
			SET @SQLStmt = @SQLStmt + ',[DataPointData03] AS [' + @DataPointName03 + ']'

			IF @DataPointName04 IS NOT NULL
			BEGIN
				SET @SQLStmt = @SQLStmt + ',[DataPointData04] AS [' + @DataPointName04 + ']'

				IF @DataPointName05 IS NOT NULL
				BEGIN
					SET @SQLStmt = @SQLStmt + ',[DataPointData05] AS [' + @DataPointName05 + ']'

					IF @DataPointName06 IS NOT NULL
					BEGIN
						SET @SQLStmt = @SQLStmt + ',[DataPointData06] AS [' + @DataPointName06 + ']'

						IF @DataPointName07 IS NOT NULL
						BEGIN
							SET @SQLStmt = @SQLStmt + ',[DataPointData07] AS [' + @DataPointName07 + ']'

							IF @DataPointName08 IS NOT NULL
							BEGIN
								SET @SQLStmt = @SQLStmt + ',[DataPointData08] AS [' + @DataPointName08 + ']'

								IF @DataPointName09 IS NOT NULL
								BEGIN
									SET @SQLStmt = @SQLStmt + ',[DataPointData09] AS [' + @DataPointName09 + ']'
								END
							END
						END
					END
				END
			END
		END
	END

	SET @SQLStmt = @SQLStmt + ' FROM [dbo].[DefTestDataSetData] WITH (NOLOCK) WHERE [DefTestDataSetID] = ' + CAST(@ID as Varchar) + ' ORDER BY [SeqNo]'
	
	EXEC (@SQLStmt)
END
GO


/* 
	Simply creates a new Test Machine record and returns the identity
*/
IF (object_id('dbo.PortalAddTestMachine')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestMachine
GO
CREATE PROCEDURE dbo.PortalAddTestMachine
(
        @MachineAddress varchar(30),
        @IsVirtual bit = 0,
		@VirtualName varchar(255) = NULL,
		@VirtualMaxGuests int = NULL,
        @Details varchar(255),
        @MachineState bit = 0,
        @CreatedBy varchar(255) = NULL,
		@CreatedDate datetime = NULL,
		@TestMachineID int = 0,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid
	--IF '' = RTRIM(@Details)
	--BEGIN
	--	RAISERROR('@Details cannot be blank.', 18, 1)
	--	IF @@Error != 0 
	--		RETURN 
	--END
	IF '' = RTRIM(@MachineAddress)
	BEGIN
		RAISERROR('@MachineAddress cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	IF @CreatedDate IS NULL
		SET @CreatedDate = GETDATE()

	-- Check for update
	IF @TestMachineID <> 0
	BEGIN
		UPDATE [dbo].[DefTestMachines]
			SET
				 [Location] = @MachineAddress
				,[IsVirtual] = @IsVirtual
				,[VirtualName] = @VirtualName
				,[VirtualMaxGuests] = @VirtualMaxGuests
				,[Details] = @Details
				,[Active] = @MachineState
				,[LastUpdatedDate] = @CreatedDate
				,[LastUpdatedBy] = @CreatedBy
			WHERE [DefTestMachinesID]=@TestMachineID
		RETURN
	END
	
	--- Check for duplicate
	IF EXISTS (SELECT 1 FROM dbo.DefTestMachines WITH (NOLOCK) WHERE Location = @MachineAddress AND VirtualName = @VirtualName)
	--- Error
	BEGIN
		RAISERROR('@This test machine already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END
	ELSE
		--- Insert
		INSERT INTO [dbo].[DefTestMachines]
				([Location]
				,[IsVirtual]
				,[VirtualName]
				,[VirtualMaxGuests]
				,[Details]
				,[Active]
				,[LastUpdatedDate]
				,[LastUpdatedBy]
				)
			VALUES
				(@MachineAddress
				,@IsVirtual
				,@VirtualName
				,@VirtualMaxGuests
				,@Details
				,@MachineState
				,@CreatedDate
				,@CreatedBy
				)

	SET @OutIdentity = SCOPE_IDENTITY()
	
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Set Test Machine record active or inactive
*/
IF (object_id('dbo.PortalModifyTestMachineState')) IS NOT NULL
	DROP PROCEDURE dbo.PortalModifyTestMachineState
GO
CREATE PROCEDURE dbo.PortalModifyTestMachineState
(
        @MachineState bit,
		@TestMachineID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid
	IF @TestMachineID IS NULL
	BEGIN
		RAISERROR('@TestMachineID must be provided.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL

	--- Update
	UPDATE [dbo].[DefTestMachines]
		SET  Active = @MachineState
			,LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE
			DefTestMachinesID = @TestMachineID
	
	RETURN(0)
END
GO

/* 
	Returns list of Test Machines
*/
IF (object_id('dbo.PortalGetTestMachineList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestMachineList
GO
CREATE PROCEDURE dbo.PortalGetTestMachineList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 [DefTestMachinesID] as 'ID'
		,[Location] as 'Address'
		,(CASE [IsVirtual]
			WHEN 1 THEN 'Yes'
			ELSE 'No' 
			END
		 ) as 'IsVirtual'
		,[VirtualName] as 'VirtualName'
		,[VirtualMaxGuests] as 'VMMaxGuests'
		,(CASE [Active]
			WHEN 1 THEN 'Active'
			ELSE 'Inactive' 
			END
		 ) as 'State'
		,[Details] as 'Details'
		,[LastUpdatedDate] as 'Last Updated'
		,[LastUpdatedBy] as 'Last Updated By'
	FROM dbo.DefTestMachines WITH (NOLOCK)
	ORDER BY [Active] DESC, [Location]

END
GO

/* 
	Simply creates a new Test Machine Group record and returns the identity
*/
IF (object_id('dbo.PortalAddTestMachineGroup')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddTestMachineGroup
GO
CREATE PROCEDURE dbo.PortalAddTestMachineGroup
(
        @Name varchar(255),
        @Description varchar(1024) = NULL,
        @CreatedBy varchar(255) = NULL,
		@CreatedDate datetime = NULL,
		@FolderID int = NULL,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid TestSet Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('@Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	--- Check for duplicate
	IF EXISTS (SELECT 1 FROM dbo.DefTestMachineGroup WITH (NOLOCK) WHERE TestMachineGroupName = @Name AND Active=1)
	BEGIN
		RAISERROR('@Name already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	IF @CreatedDate IS NULL
		SET @CreatedDate = GETDATE()
	IF @FolderID IS NULL
		EXEC GetRootFolderID @ObjectType = 'DefTestMachineGroup', @OutIdentity = @FolderID OUTPUT

	--- Insert
	INSERT INTO [dbo].[DefTestMachineGroup]
			([DefFolderID]
			,[TestMachineGroupName]
			,[Description]
			,[Active]
			,[LastUpdatedDate]
			,[LastUpdatedBy]
			)
		VALUES
			(@FolderID
			,@Name
			,@Description
			,1
			,@CreatedDate
			,@CreatedBy
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Adds a Test Machine record to an existing Test Machine Group by ID
*/
IF (object_id('dbo.PortalAssignTestMachineGroupItemByID')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAssignTestMachineGroupItemByID
GO
CREATE PROCEDURE dbo.PortalAssignTestMachineGroupItemByID
(
        @TestMachineGroupID int,
		@TestMachineID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Test Machine Group ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestMachineGroup WITH (NOLOCK) WHERE DefTestMachineGroupID = @TestMachineGroupID AND Active=1)
	BEGIN
		RAISERROR('Invalid TestMachineGroupID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	--- Check for valid Test Machine ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestMachines WITH (NOLOCK) WHERE DefTestMachinesID = @TestMachineID)
	BEGIN
		RAISERROR('Invalid @TestMachineID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	--- Check if already added
	IF EXISTS (SELECT 1 FROM dbo.DefTestMachineGroupData WITH (NOLOCK) WHERE DefTestMachineGroupID = @TestMachineGroupID AND DefTestMachinesID = @TestMachineID)
		RETURN(0)

	--- Add the test case to the test set
	INSERT INTO dbo.DefTestMachineGroupData
	(
		 [DefTestMachineGroupID]
		,[DefTestMachinesID]
	)
	VALUES
	(
		 @TestMachineGroupID
		,@TestMachineID
	)
	
	--- Revise last update fields
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.DefTestMachineGroup
		SET 
			 LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefTestMachineGroupID = @TestMachineGroupID
	
	RETURN(0)
END
GO

/* 
	Adds a Test Machine record to an existing Test Machine Group by Name
*/
IF (object_id('dbo.PortalAssignTestMachineGroupItemByName')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAssignTestMachineGroupItemByName
GO
CREATE PROCEDURE dbo.PortalAssignTestMachineGroupItemByName
(
        @TestMachineGroupName varchar(255),
		@TestMachineAddress varchar(255),
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @pTestMachineGroupID int = NULL, @pTestMachineID int = NULL

	SELECT @pTestMachineGroupID = DefTestMachineGroupID FROM dbo.DefTestMachineGroup WITH (NOLOCK) WHERE TestMachineGroupName = @TestMachineGroupName AND Active=1
	IF @pTestMachineGroupID IS NULL
	BEGIN
		RAISERROR('Invalid @TestMachineGroupName.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

	SELECT @pTestMachineID = DefTestMachinesID FROM dbo.DefTestMachines WITH (NOLOCK) WHERE Location = @TestMachineAddress
	IF @pTestMachineID IS NULL
	BEGIN
		RAISERROR('Invalid @TestMachineAddress.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	
	EXEC PortalAssignTestMachineGroupItemByID
		 @TestMachineGroupID = @pTestMachineGroupID
		,@TestMachineID = @pTestMachineID
		,@UpdatedBy = @UpdatedBy
	
	RETURN(0)
END
GO

/* 
	Removes a Test Machine record from an existing Test Machine Group
*/
IF (object_id('dbo.PortalRemoveTestMachineGroupItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalRemoveTestMachineGroupItem
GO
CREATE PROCEDURE dbo.PortalRemoveTestMachineGroupItem
(
        @TestMachineGroupID int,
		@TestMachineID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Test Machine Group ID
	IF NOT EXISTS (SELECT 1 FROM dbo.DefTestMachineGroup WITH (NOLOCK) WHERE DefTestMachineGroupID = @TestMachineGroupID AND Active=1)
	BEGIN
		RAISERROR('Invalid TestMachineGroupID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

	--- Remove
	DELETE FROM dbo.DefTestMachineGroupData WHERE DefTestMachineGroupID = @TestMachineGroupID AND DefTestMachinesID = @TestMachineID

	--- Revise last update fields
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.DefTestMachineGroup
		SET 
			 LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefTestMachineGroupID = @TestMachineGroupID
		
	RETURN(0)
END
GO

/* 
	Returns list of Test Machine Groups
*/
IF (object_id('dbo.PortalGetTestMachineGroupList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestMachineGroupList
GO
CREATE PROCEDURE dbo.PortalGetTestMachineGroupList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 [DefTestMachineGroupID] as 'ID'
		,[TestMachineGroupName] as 'Name'
		,[Description] as 'Description'
		,[LastUpdatedDate] as 'Last Updated'
		,[LastUpdatedBy] as 'Last Updated By'
	FROM dbo.DefTestMachineGroup p WITH (NOLOCK)
	WHERE [Active]=1
	ORDER BY [TestMachineGroupName]
	
END
GO

/* 
	Returns the full list of test machines assigned to a test machine group
*/
IF (object_id('dbo.PortalGetTestMachineGroupItemList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestMachineGroupItemList
GO
CREATE PROCEDURE dbo.PortalGetTestMachineGroupItemList
(
        @TestMachineGroupID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 [DefTestMachinesID] as 'ID'
		,[Location] as 'Address'
		,[Active] as 'Active State'
		,(CASE [IsVirtual]
			WHEN 1 THEN 'Yes'
			ELSE 'No' 
			END
		 ) as 'IsVirtual'
		,[VirtualName] as 'VirtualName'
		,[Details] as 'Details'
		,[LastUpdatedDate] as 'Last Updated'
		,[LastUpdatedBy] as 'Last Updated By'
		FROM dbo.DefTestMachines WITH (NOLOCK)
		WHERE [DefTestMachinesID] IN (	SELECT [DefTestMachinesID] 
									FROM [dbo].[DefTestMachineGroupData] WITH (NOLOCK)
									WHERE [DefTestMachineGroupID] = @TestMachineGroupID
								  )
		ORDER BY [Active] DESC, [Location]

END
GO

/* 
	Returns list of Test Machines
*/
IF (object_id('dbo.PortalGetTestMachineGroupMembers')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestMachineGroupMembers
GO
CREATE PROCEDURE dbo.PortalGetTestMachineGroupMembers
(
        @TestMachineGroupID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 (SELECT 'Yes' FROM DefTestMachineGroupData c WITH (NOLOCK)
			WHERE p.DefTestMachinesID = c.DefTestMachinesID AND c.DefTestMachineGroupID = @TestMachineGroupID
		 ) As 'Member'
		,p.[DefTestMachinesID] as 'ID'
		,p.[Location] as 'Address'
		,(CASE p.[Active]
			WHEN 1 THEN 'Active'
			ELSE 'Inactive' 
			END
		 ) as 'State'
		,(CASE [IsVirtual]
			WHEN 1 THEN 'Yes'
			ELSE 'No' 
			END
		 ) as 'IsVirtual'
		,[VirtualName] as 'VirtualName'
		,p.[Details] as 'Details'
		FROM dbo.DefTestMachines p WITH (NOLOCK)
								  
		ORDER BY p.[Active] DESC, p.[IsVirtual], p.[Location], p.[VirtualName]

END
GO


/* 
	Returns list of Base State Parameters
*/
IF (object_id('dbo.PortalGetBaseStateDefinedParamsList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetBaseStateDefinedParamsList
GO
CREATE PROCEDURE dbo.PortalGetBaseStateDefinedParamsList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 [BaseStateParamsID] as 'ID'
		,[BaseStateParamName] as 'Name'
		,[Required] as 'Required'
		,[DefListID] as 'Valid Values List'
		,[DefaultValue] as 'Default Value'
	FROM dbo.BaseStateParams WITH (NOLOCK)
	WHERE [Active]=1
	ORDER BY [BaseStateParamsID]
	
END
GO

/* 
	Simply creates a new Base State record and returns the identity
*/
IF (object_id('dbo.PortalAddBaseState')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddBaseState
GO
CREATE PROCEDURE dbo.PortalAddBaseState
(
        @Name varchar(255),
        @Description varchar(1024) = NULL,
		@CopyItemsFromID int = NULL,
        @CreatedBy varchar(255) = NULL,
		@CreatedDate datetime = NULL,
		@FolderID int = NULL,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('@Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	--- Check for duplicate
	IF EXISTS (SELECT 1 FROM dbo.DefBaseState WITH (NOLOCK) WHERE BaseStateName = @Name AND Active=1)
	BEGIN
		RAISERROR('@Name already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	IF @CreatedDate IS NULL
		SET @CreatedDate = GETDATE()
	IF @FolderID IS NULL
		EXEC GetRootFolderID @ObjectType = 'DefBaseState', @OutIdentity = @FolderID OUTPUT

	--- Insert
	INSERT INTO [dbo].[DefBaseState]
			([DefFolderID]
			,[BaseStateName]
			,[Description]
			,[Active]
			,[LastUpdatedDate]
			,[LastUpdatedBy]
			)
		VALUES
			(@FolderID
			,@Name
			,@Description
			,1
			,@CreatedDate
			,@CreatedBy
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	
	IF @CopyItemsFromID IS NOT NULL
	BEGIN
		--- Copy parameters from another base state
		INSERT INTO [dbo].[DefBaseStateData]
		(
			 [DefBaseStateID]
			,[BaseStateParamsID]
			,[ParamValue]
		)
		SELECT
			 @OutIdentity
			,BaseStateParamsID
			,ParamValue
		FROM dbo.[DefBaseStateData] WITH (NOLOCK)
		WHERE DefBaseStateID = @CopyItemsFromID
	END
	ELSE
	BEGIN
		--- Pre-populate required parameters
		INSERT INTO [dbo].[DefBaseStateData]
		(
			 [DefBaseStateID]
			,[BaseStateParamsID]
			,[ParamValue]
		)
		SELECT
			 @OutIdentity
			,BaseStateParamsID
			,ISNULL(DefaultValue, '')
		FROM dbo.BaseStateParams WITH (NOLOCK)
		WHERE [Active]=1 AND [Required]=1
	END
	
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Removes a Parameter from an existing Base State
*/
IF (object_id('dbo.PortalDeleteBaseState')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDeleteBaseState
GO
CREATE PROCEDURE dbo.PortalDeleteBaseState
(
        @BaseStateID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT 1 FROM dbo.DefBaseState WITH (NOLOCK) WHERE DefBaseStateID = @BaseStateID)
	BEGIN
		RAISERROR('Invalid @BaseStateID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

		--- Set Inactive
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.DefBaseState
		SET 
			Active = 0
			,LastUpdatedBy = @UpdatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefBaseStateID = @BaseStateID
	
	RETURN(0)
END
GO

/* 
	Adds a Parameter to an existing Base State
*/
IF (object_id('dbo.PortalBaseStateAddParam')) IS NOT NULL
	DROP PROCEDURE dbo.PortalBaseStateAddParam
GO
CREATE PROCEDURE dbo.PortalBaseStateAddParam
(
        @BaseStateID int,
		@ParamValue varchar(255),
		-- here you can provide the ID or name
		@ParamID int = NULL,
		@ParamName varchar(255) = NULL,
        @CreatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT 1 FROM dbo.DefBaseState WITH (NOLOCK) WHERE DefBaseStateID = @BaseStateID AND Active=1)
	BEGIN
		RAISERROR('Invalid @BaseStateID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

	IF @ParamID IS NULL
	BEGIN
		IF @ParamName IS NULL
		BEGIN
			RAISERROR('You must provide either a @ParamID or @ParamName.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
		--- Get the ID from the name
		SELECT @ParamID = BaseStateParamsID FROM dbo.BaseStateParams WITH (NOLOCK) WHERE BaseStateParamName = @ParamName
		IF @ParamID IS NULL
		BEGIN
			RAISERROR('Invalid @ParamName.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
	END
	ELSE
		IF NOT EXISTS (SELECT 1 FROM dbo.BaseStateParams WITH (NOLOCK) WHERE BaseStateParamsID = @ParamID AND Active=1)
		BEGIN
			RAISERROR('Invalid @ParamID.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
	
	--- Check if already exists
	IF EXISTS (SELECT 1 FROM dbo.DefBaseStateData WITH (NOLOCK) WHERE BaseStateParamsID = @ParamID AND DefBaseStateID = @BaseStateID)
	BEGIN
		--- Update the value
		UPDATE [dbo].[DefBaseStateData]
			SET [ParamValue] = @ParamValue
			WHERE BaseStateParamsID = @ParamID AND DefBaseStateID = @BaseStateID
	END
	ELSE
	BEGIN
		--- Add the parameter
		INSERT INTO [dbo].[DefBaseStateData]
		(
			 [DefBaseStateID]
			,[BaseStateParamsID]
			,[ParamValue]
		)
		VALUES
		(
			 @BaseStateID
			,@ParamID
			,@ParamValue
		)
	END

	
	--- Revise last update fields
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	UPDATE dbo.DefBaseState
		SET 
			 LastUpdatedBy = @CreatedBy
			,LastUpdatedDate = GETDATE()
		WHERE DefBaseStateID = @BaseStateID
	
	RETURN(0)
END
GO

/* 
	Removes a Parameter from an existing Base State
*/
IF (object_id('dbo.PortalBaseStateDeleteParam')) IS NOT NULL
	DROP PROCEDURE dbo.PortalBaseStateDeleteParam
GO
CREATE PROCEDURE dbo.PortalBaseStateDeleteParam
(
		@ParamID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @ParamID IS NULL
	BEGIN
		RAISERROR('Invalid @ParamID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END
	
	declare @BaseStateID int
	SELECT @BaseStateID=DefBaseStateID FROM dbo.DefBaseStateData WHERE DefBaseStateDataID = @ParamID

	IF @BaseStateID IS NOT NULL
	BEGIN
		--- Remove
		DELETE FROM dbo.DefBaseStateData WHERE DefBaseStateDataID = @ParamID
	
		--- Revise last update fields
		IF @UpdatedBy IS NOT NULL
			IF '' = RTRIM(@UpdatedBy)
				SET @UpdatedBy = NULL
		UPDATE dbo.DefBaseState
			SET 
				 LastUpdatedBy = @UpdatedBy
				,LastUpdatedDate = GETDATE()
			WHERE DefBaseStateID = @BaseStateID
	END
	
	RETURN(0)
END
GO

/* 
	Update a Parameter Value in an existing Base State
*/
IF (object_id('dbo.PortalBaseStateUpdateParam')) IS NOT NULL
	DROP PROCEDURE dbo.PortalBaseStateUpdateParam
GO
CREATE PROCEDURE dbo.PortalBaseStateUpdateParam
(
        @BaseStateID int,
		@ParamValue varchar(255),
		-- here you can provide the ID or name
		@ParamID int = NULL,
		@ParamName varchar(255) = NULL,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	EXEC PortalBaseStateAddParam
		 @BaseStateID
		,@ParamValue
		,@ParamID
		,@ParamName
		,@UpdatedBy
	
	RETURN(0)
END
GO

/* 
	Returns list of Base States
*/
IF (object_id('dbo.PortalGetBaseStateList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetBaseStateList
GO
CREATE PROCEDURE dbo.PortalGetBaseStateList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.DefBaseStateID as 'ID'
		,p.BaseStateName as 'Name'
		,p.Description as 'Description'
		,p.LastUpdatedDate as 'Last Updated'
		,p.LastUpdatedBy as 'Last Updated By'
		,(SELECT COUNT(c.DefBaseStateDataID) FROM DefBaseStateData c WHERE c.DefBaseStateID = p.DefBaseStateID)
			as 'Param Count'
	FROM dbo.DefBaseState p WITH (NOLOCK)
	WHERE p.Active=1
	ORDER BY p.BaseStateName

END
GO

/* 
	Returns list of Parameters added to a Base State
*/
IF (object_id('dbo.PortalGetBaseStateParamList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetBaseStateParamList
GO
CREATE PROCEDURE dbo.PortalGetBaseStateParamList
(
        @BaseStateID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.DefBaseStateDataID as 'ID'
		,(SELECT c.BaseStateParamName FROM BaseStateParams c WHERE c.BaseStateParamsID = p.BaseStateParamsID)
			as 'Name'
		,p.ParamValue as 'Value'
	FROM dbo.DefBaseStateData p WITH (NOLOCK)
	WHERE p.DefBaseStateID = @BaseStateID
	ORDER BY p.DefBaseStateDataID

END
GO

/* 
	Returns list of Parameters available to a Base State
*/
IF (object_id('dbo.PortalGetBaseStateOpenParamList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetBaseStateOpenParamList
GO
CREATE PROCEDURE dbo.PortalGetBaseStateOpenParamList
(
        @BaseStateID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.[BaseStateParamsID] as 'ID'
		,p.[BaseStateParamName] as 'Name'
		,p.[Required] as 'Required'
		,p.[DefListID] as 'Valid Values List'
		,p.[DefaultValue] as 'Default Value'
	FROM dbo.BaseStateParams p WITH (NOLOCK)
	WHERE p.[Active]=1 AND
		p.[BaseStateParamsID] NOT IN 
			(SELECT BaseStateParamsID
					FROM dbo.DefBaseStateData WITH (NOLOCK)
					WHERE DefBaseStateID = @BaseStateID)
	ORDER BY p.[BaseStateParamsID]
	
END
GO

/* 
	Returns default value of a Parameter
*/
IF (object_id('dbo.PortalGetBaseStateParamDefaultValue')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetBaseStateParamDefaultValue
GO
CREATE PROCEDURE dbo.PortalGetBaseStateParamDefaultValue
(
		-- here you can provide the ID or name
		@ParamID int = NULL,
		@ParamName varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @ParamID IS NULL
	BEGIN
		IF @ParamName IS NULL
		BEGIN
			RAISERROR('You must provide either a @ParamID or @ParamName.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
		--- Get the ID from the name
		SELECT @ParamID = BaseStateParamsID FROM dbo.BaseStateParams WITH (NOLOCK) WHERE BaseStateParamName = @ParamName AND Active=1
		IF @ParamID IS NULL
		BEGIN
			RAISERROR('Invalid @ParamName.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
	END
	ELSE
		IF NOT EXISTS (SELECT 1 FROM dbo.BaseStateParams WITH (NOLOCK) WHERE BaseStateParamsID = @ParamID AND Active=1)
		BEGIN
			RAISERROR('Invalid @ParamID.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
	
	SELECT ISNULL([DefaultValue], '') as 'DefaultValue' FROM dbo.BaseStateParams WITH (NOLOCK) WHERE [BaseStateParamsID] = @ParamID
	
END
GO

/* 
	Returns list of valid Parameter values
*/
IF (object_id('dbo.PortalGetBaseStateParamValidValues')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetBaseStateParamValidValues
GO
CREATE PROCEDURE dbo.PortalGetBaseStateParamValidValues
(
		-- here you can provide the ID or name
		@ParamID int = NULL,
		@ParamName varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @ParamID IS NULL
	BEGIN
		IF @ParamName IS NULL
		BEGIN
			RAISERROR('You must provide either a @ParamID or @ParamName.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
		--- Get the ID from the name
		SELECT @ParamID = BaseStateParamsID FROM dbo.BaseStateParams WITH (NOLOCK) WHERE BaseStateParamName = @ParamName AND Active=1
		IF @ParamID IS NULL
		BEGIN
			RAISERROR('Invalid @ParamName.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
	END
	ELSE
		IF NOT EXISTS (SELECT 1 FROM dbo.BaseStateParams WITH (NOLOCK) WHERE BaseStateParamsID = @ParamID AND Active=1)
		BEGIN
			RAISERROR('Invalid @ParamID.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
	
	DECLARE @ListID int = NULL
	SELECT @ListID = [DefListID] FROM dbo.BaseStateParams WITH (NOLOCK) WHERE [BaseStateParamsID] = @ParamID
	
	SELECT
		 ListItemValue as 'Value'
	FROM dbo.DefListItems WITH (NOLOCK)
	WHERE [DefListID] = @ListID
	ORDER BY [ListItemValue]
	
END
GO


/* ********************************************************************* */
/* 
	Simply creates a new Notification Group record and returns the identity
*/
IF (object_id('dbo.PortalAddNotificationGroup')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddNotificationGroup
GO
CREATE PROCEDURE dbo.PortalAddNotificationGroup
(
        @Name varchar(255),
        @Description varchar(1024) = NULL,
        @CreatedBy varchar(255) = NULL,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('@Name cannot be blank.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	--- Check for duplicate
	IF EXISTS (SELECT 1 FROM dbo.DefNotificationGroup WITH (NOLOCK) WHERE NotificationGroupName = @Name AND Active=1)
	BEGIN
		RAISERROR('@Name already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL

	--- Insert
	INSERT INTO [dbo].[DefNotificationGroup]
			([NotificationGroupName]
			,[Description]
			,[Active]
			,[CreatedDate]
			,[CreatedBy]
			)
		VALUES
			(@Name
			,@Description
			,1
			,GETDATE()
			,@CreatedBy
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Adds an Item to an existing Notification Group
*/
IF (object_id('dbo.PortalNotificationGroupAddItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalNotificationGroupAddItem
GO
CREATE PROCEDURE dbo.PortalNotificationGroupAddItem
(
        @NotificationGroupID int,
		@UserID varchar(255),
		@EmailAddress varchar(255),
		@FriendlyName varchar(255),
        @CreatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT 1 FROM dbo.DefNotificationGroup WITH (NOLOCK) WHERE DefNotificationGroupID = @NotificationGroupID AND Active=1)
	BEGIN
		RAISERROR('Invalid @NotificationGroupID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

	IF '' = RTRIM(@UserID)
	
	--- Fixup fields
	IF @EmailAddress IS NOT NULL
		IF '' = RTRIM(@EmailAddress)
			SET @EmailAddress = NULL
	IF @FriendlyName IS NOT NULL
		IF '' = RTRIM(@FriendlyName)
			SET @FriendlyName = NULL
	
	--- Check if already exists
	IF EXISTS (SELECT 1 FROM dbo.DefNotificationGroupData WITH (NOLOCK) WHERE UserID = @UserID AND DefNotificationGroupID = @NotificationGroupID)
	BEGIN
		--- Update the value
		UPDATE [dbo].[DefNotificationGroupData]
			SET [EmailAddress] = @EmailAddress, [FriendlyName] = @FriendlyName
			WHERE UserID = @UserID AND DefNotificationGroupID = @NotificationGroupID
	END
	ELSE
	BEGIN
		--- Add the parameter
		INSERT INTO [dbo].[DefNotificationGroupData]
		(
			 [DefNotificationGroupID]
			,[UserID]
			,[EmailAddress]
			,[FriendlyName]
		)
		VALUES
		(
			 @NotificationGroupID
			,@UserID
			,@EmailAddress
			,@FriendlyName
		)
	END

	
	--- Revise last update fields
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	UPDATE dbo.DefNotificationGroup
		SET 
			 CreatedBy = @CreatedBy
			,CreatedDate = GETDATE()
		WHERE DefNotificationGroupID = @NotificationGroupID
	
	RETURN(0)
END
GO

/* 
	Removes an Item from an existing Notification Group
*/
IF (object_id('dbo.PortalNotificationGroupDeleteItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalNotificationGroupDeleteItem
GO
CREATE PROCEDURE dbo.PortalNotificationGroupDeleteItem
(
        @NotificationGroupID int,
		@UserID varchar(255),
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT 1 FROM dbo.DefNotificationGroup WITH (NOLOCK) WHERE DefNotificationGroupID = @NotificationGroupID AND Active=1)
	BEGIN
		RAISERROR('Invalid @NotificationGroupID.', 18, 1)
		IF @@Error != 0 
			RETURN(1) 
	END

	--- Remove
	DELETE FROM DefNotificationGroupData WHERE UserID = @UserID AND DefNotificationGroupID = @NotificationGroupID
	
	--- Revise last update fields
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.DefNotificationGroup
		SET 
			 CreatedBy = @UpdatedBy
			,CreatedDate = GETDATE()
		WHERE DefNotificationGroupID = @NotificationGroupID
	
	RETURN(0)
END
GO

/* 
	Update an Item in an existing Notification Group
*/
IF (object_id('dbo.PortalNotificationGroupUpdateItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalNotificationGroupUpdateItem
GO
CREATE PROCEDURE dbo.PortalNotificationGroupUpdateItem
(
        @NotificationGroupID int,
		@UserID varchar(255),
		@EmailAddress varchar(255),
		@FriendlyName varchar(255),
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	EXEC PortalNotificationGroupAddItem
		 @NotificationGroupID
		,@UserID
		,@EmailAddress
		,@FriendlyName
		,@UpdatedBy
	
	RETURN(0)
END
GO

/* 
	Returns list of Notification Groups
*/
IF (object_id('dbo.PortalGetNotificationGroupList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetNotificationGroupList
GO
CREATE PROCEDURE dbo.PortalGetNotificationGroupList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.DefNotificationGroupID as 'ID'
		,p.NotificationGroupName as 'Name'
		,p.Description as 'Description'
		,p.CreatedDate as 'Last Updated'
		,p.CreatedBy as 'Last Updated By'
		,(SELECT COUNT(c.DefNotificationGroupDataID) FROM DefNotificationGroupData c WHERE p.DefNotificationGroupID = c.DefNotificationGroupID)
			as 'Item Count'
	FROM dbo.DefNotificationGroup p WITH (NOLOCK)
	WHERE p.Active=1
	ORDER BY p.NotificationGroupName

END
GO

/* 
	Returns list of Items added to a Notification Group
*/
IF (object_id('dbo.PortalGetNotificationGroupItemList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetNotificationGroupItemList
GO
CREATE PROCEDURE dbo.PortalGetNotificationGroupItemList
(
        @NotificationGroupID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 DefNotificationGroupDataID as 'ID'
		,UserID as 'User ID'
		,EmailAddress as 'Email Address'
		,FriendlyName as 'Friendly Name'
	FROM dbo.DefNotificationGroupData WITH (NOLOCK)
		WHERE DefNotificationGroupID = @NotificationGroupID
	ORDER BY UserID

END
GO

/* 
	Simply creates a new DefRun record and returns the identity
*/
IF (object_id('dbo.PortalAddDefRun')) IS NOT NULL
	DROP PROCEDURE dbo.PortalAddDefRun
GO
CREATE PROCEDURE dbo.PortalAddDefRun
(
        @Name varchar(255),
        @Description varchar(1024) = NULL,
        @NotificationGroupID int = NULL,
        @CreatedBy varchar(255) = NULL,
		@FolderID int = NULL,
		@ReturnIdentity bit = 0,
		@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('@Name cannot be blank.', 18, 1)
		IF @@Error != 0
			RETURN 
	END

	--- Check for duplicate
	IF EXISTS (SELECT 1 FROM dbo.DefRun WITH (NOLOCK) WHERE RunName = @Name AND Active=1)
	BEGIN
		RAISERROR('@Name already exists.', 18, 1)
		IF @@Error != 0 
			RETURN 
	END

	IF @Description IS NOT NULL
		IF '' = RTRIM(@Description)
			SET @Description = NULL
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	IF @FolderID IS NULL
		EXEC GetRootFolderID @ObjectType = 'DefRun', @OutIdentity = @FolderID OUTPUT

	--- Insert
	INSERT INTO [dbo].[DefRun]
			([DefFolderID]
			,[RunName]
			,[Description]
			,[DefNotificationGroupID]
			,[Active]
			,[CreatedDate]
			,[CreatedBy]
			)
		VALUES
			(@FolderID
			,@Name
			,@Description
			,@NotificationGroupID
			,1
			,GETDATE()
			,@CreatedBy
			)

	SET @OutIdentity = SCOPE_IDENTITY()
	
	IF @ReturnIdentity = 1
		SELECT @OutIdentity
END
GO

/* 
	Adds a or updates a data row to an existing DefRun
*/
IF (object_id('dbo.PortalDefRunUpdateItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDefRunUpdateItem
GO
CREATE PROCEDURE dbo.PortalDefRunUpdateItem
(
		@ID int = NULL,
		@RunID int = NULL,
		@TestSetID int,
		@BaseStateID int,
		@TestMachineGroupID int,
        @CreatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	IF @ID IS NULL
	BEGIN
		-- Adding a new one
		IF NOT EXISTS (SELECT 1 FROM dbo.DefRun WITH (NOLOCK) WHERE DefRunID = @RunID AND Active=1)
		BEGIN
			RAISERROR('Invalid @RunID.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END
		
		INSERT INTO [dbo].[DefRunData]
		(
			 [DefRunID]
			,[DefTestSetID]
			,[DefBaseStateID]
			,[DefTestMachineGroupID]
		)
		VALUES
		(
			 @RunID
			,@TestSetID
			,@BaseStateID
			,@TestMachineGroupID
		)
		
	END
	ELSE
	BEGIN
		-- Updating an existing one
		IF NOT EXISTS (SELECT 1 FROM dbo.DefRunData WITH (NOLOCK) WHERE DefRunDataID = @ID)
		BEGIN
			RAISERROR('Invalid @ID.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END

		UPDATE [dbo].[DefRunData]
			SET
				 [DefTestSetID] = @TestSetID
				,[DefBaseStateID] = @BaseStateID
				,[DefTestMachineGroupID] = @TestMachineGroupID
			WHERE DefRunDataID = @ID

		SELECT @RunID = DefRunID FROM dbo.DefRunData WITH (NOLOCK) WHERE DefRunDataID = @ID
	END
	
	--- Revise last update fields
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	UPDATE dbo.DefRun
		SET 
			 CreatedBy = @CreatedBy
			,CreatedDate = GETDATE()
		WHERE DefRunID = @RunID
	
	RETURN(0)
END
GO

/* 
	Removes an item from an existing DefRun
*/
IF (object_id('dbo.PortalDefRunDeleteItem')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDefRunDeleteItem
GO
CREATE PROCEDURE dbo.PortalDefRunDeleteItem
(
		@ID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RunID int = NULL
	SELECT @RunID = DefRunID FROM dbo.DefRunData WITH (NOLOCK) WHERE DefRunDataID = @ID

	IF @RunID IS NOT NULL
	BEGIN
		--- Remove
		DELETE FROM dbo.DefRunData WHERE DefRunDataID = @ID
	
		--- Revise last update fields
		IF @UpdatedBy IS NOT NULL
			IF '' = RTRIM(@UpdatedBy)
				SET @UpdatedBy = NULL
		UPDATE dbo.DefRun
			SET 
				 CreatedBy = @UpdatedBy
				,CreatedDate = GETDATE()
			WHERE DefRunID = @RunID
	END
	
	RETURN(0)
END
GO

/* 
	Returns list of DefRun
*/
IF (object_id('dbo.PortalGetDefRunList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetDefRunList
GO
CREATE PROCEDURE dbo.PortalGetDefRunList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.DefRunID as 'ID'
		,p.RunName as 'Name'
		,p.Description as 'Description'
		,(SELECT ISNULL(c.NotificationGroupName, '') FROM DefNotificationGroup c  WITH (NOLOCK) WHERE c.DefNotificationGroupID = p.DefNotificationGroupID)
			as 'Notification Group'
		,p.CreatedDate as 'Last Updated'
		,p.CreatedBy as 'Last Updated By'
		,(SELECT COUNT(c.DefRunDataID) FROM DefRunData c WITH (NOLOCK) WHERE c.DefRunID = p.DefRunID)
			as 'Item Count'
	FROM dbo.DefRun p WITH (NOLOCK)
	WHERE p.Active=1
	ORDER BY p.RunName

END
GO

/* 
	Returns list of Items assigned to a DefRun
*/
IF (object_id('dbo.PortalDefRunItemList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDefRunItemList
GO
CREATE PROCEDURE dbo.PortalDefRunItemList
(
        @ID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.DefRunDataID as 'ID'
		,(SELECT c.TestSetName FROM DefTestSet c WITH (NOLOCK) WHERE c.DefTestSetID = p.DefTestSetID)
			as 'Test Set'
		,(SELECT c.BaseStateName FROM DefBaseState c  WITH (NOLOCK) WHERE c.DefBaseStateID = p.DefBaseStateID)
			as 'Base State'
		,(SELECT c.TestMachineGroupName FROM DefTestMachineGroup c WITH (NOLOCK) WHERE c.DefTestMachineGroupID = p.DefTestMachineGroupID)
			as 'Test Machine Group'
	FROM dbo.DefRunData p WITH (NOLOCK)
	WHERE p.DefRunID = @ID
	ORDER BY p.DefRunDataID

END
GO

/* 
	Returns details of a single Item assigned to a DefRun
*/
IF (object_id('dbo.PortalDefRunItemDetail')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDefRunItemDetail
GO
CREATE PROCEDURE dbo.PortalDefRunItemDetail
(
		@ItemID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.DefRunDataID as 'ID'
		,(SELECT c.TestSetName FROM DefTestSet c WITH (NOLOCK) WHERE c.DefTestSetID = p.DefTestSetID)
			as 'Test Set'
		,(SELECT c.BaseStateName FROM DefBaseState c  WITH (NOLOCK) WHERE c.DefBaseStateID = p.DefBaseStateID)
			as 'Base State'
		,(SELECT c.TestMachineGroupName FROM DefTestMachineGroup c WITH (NOLOCK) WHERE c.DefTestMachineGroupID = p.DefTestMachineGroupID)
			as 'Test Machine Group'
	FROM dbo.DefRunData p WITH (NOLOCK)
	WHERE p.DefRunDataID = @ItemID

END
GO

IF (object_id('dbo.PortalUpdateRuns')) IS NOT NULL
	DROP PROCEDURE dbo.PortalUpdateRuns
GO
CREATE PROCEDURE dbo.PortalUpdateRuns
(
		@ID int = NULL,
		@Name varchar(255),
		@DefRunID int,
		@Scheduled datetime,
		@FolderID int = NULL,
        @CreatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Check for valid Name
	IF '' = RTRIM(@Name)
	BEGIN
		RAISERROR('@Name cannot be empty.', 18, 1)
		IF @@Error != 0
			RETURN 
	END

	DECLARE @TestCaseCount int

	SELECT @TestCaseCount = COUNT(c.DefTestSetCasesID) FROM DefTestSetCases c WITH (NOLOCK) 
	FULL JOIN DefRunData c2 ON c.DefTestSetID = c2.DefTestSetID
	WHERE c2.DefRunID = @DefRunID

	IF @ID IS NULL
	BEGIN

		-- Adding a new one
		--IF EXISTS (SELECT 1 FROM dbo.Runs WITH (NOLOCK) WHERE RunName = @Name AND Active=1)
		--BEGIN
		--	RAISERROR('Run already exists by that name.', 18, 1)
		--	IF @@Error != 0 
		--		RETURN(1) 
		--END
		IF @FolderID IS NULL
			EXEC GetRootFolderID @ObjectType = 'Runs:Active', @OutIdentity = @FolderID OUTPUT
		
		INSERT INTO [dbo].[Runs]
		(
			 [DefFolderID]
			,[DefRunID]
			,[RunName]
			,[Active]
			,[Scheduled]
			,[CreatedDate]
			,[TestCaseCount]
		)
		VALUES
		(
			 @FolderID
			,@DefRunID
			,@Name
			,1
			,@Scheduled
			,GETDATE()
			,@TestCaseCount
		)

		SELECT @ID = SCOPE_IDENTITY()
		
	END
	ELSE
	BEGIN
		-- Updating an existing one
		IF NOT EXISTS (SELECT 1 FROM dbo.Runs WITH (NOLOCK) WHERE RunsID = @ID AND Active = 1)
		BEGIN
			RAISERROR('Invalid @ID.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END

		IF EXISTS (SELECT 1 FROM dbo.Runs WITH (NOLOCK) WHERE RunsID = @ID AND ISNULL(InProgress,0) = 1)
		BEGIN
			RAISERROR('You cannot update a run that has already started.', 18, 1)
			IF @@Error != 0 
				RETURN(1) 
		END

		UPDATE [dbo].[Runs]
			SET
				 [DefRunID] = @DefRunID
				,[RunName] = @Name
				,[Scheduled] = @Scheduled
				,[TestCaseCount] = @TestCaseCount
			WHERE RunsID = @ID

	END
	
	--- Revise last update fields
	IF @CreatedBy IS NOT NULL
		IF '' = RTRIM(@CreatedBy)
			SET @CreatedBy = NULL
	UPDATE dbo.Runs
		SET 
			 CreatedBy = @CreatedBy
			,LastUpdated = GETDATE()
		WHERE RunsID = @ID
	
	RETURN(0)
END
GO

/* 
	Removes an item from an existing Runs
*/
IF (object_id('dbo.PortalDeleteRuns')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDeleteRuns
GO
CREATE PROCEDURE dbo.PortalDeleteRuns
(
		@ID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT @ID = RunsID FROM dbo.Runs WITH (NOLOCK) WHERE RunsID = @ID AND Active = 1 AND ISNULL(InProgress,0) <> 1

	IF @ID IS NOT NULL
	BEGIN

		UPDATE [dbo].[Runs]
			SET
				 [Active] = 0
			WHERE RunsID = @ID
	
		--- Revise last update fields
		IF @UpdatedBy IS NOT NULL
			IF '' = RTRIM(@UpdatedBy)
				SET @UpdatedBy = NULL
		UPDATE dbo.Runs
			SET 
				 @UpdatedBy = @UpdatedBy
				,LastUpdated = GETDATE()
			WHERE RunsID = @ID
	END

	RETURN(0)
END
GO

/* 
	Removes an item from an existing Runs (journaled)
*/
IF (object_id('dbo.PortalDeleteRunsJournal')) IS NOT NULL
	DROP PROCEDURE dbo.PortalDeleteRunsJournal
GO
CREATE PROCEDURE dbo.PortalDeleteRunsJournal
(
		@RunsID int,
        @UpdatedBy varchar(255) = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	--- Revise Runs fields
	IF @UpdatedBy IS NOT NULL
		IF '' = RTRIM(@UpdatedBy)
			SET @UpdatedBy = NULL
	UPDATE dbo.Runs
		SET 
			 	Active = 0
			,@UpdatedBy = @UpdatedBy
			,LastUpdated = GETDATE()
		WHERE RunsID = @RunsID

	--- Remove journaled runs data

	DELETE FROM ResTestEvidence
	WHERE SourceTable = 'ResTestCase'
			AND SourceID IN
				(SELECT tc.ResTestCaseID
				 FROM ResTestCase tc WITH (NOLOCK)
				 WHERE tc.ResTestSetID IN
				  (SELECT ts.ResTestSetID
				   FROM ResTestSet ts WITH (NOLOCK)
				   WHERE ts.RunsID = @RunsID)
				)

	DELETE FROM ResTestEvidence
	WHERE SourceTable = 'ResTestStep'
			AND SourceID IN
				(SELECT tp.ResTestStepID
				   FROM ResTestStep tp WITH (NOLOCK)
				   WHERE tp.ResTestCaseID IN
					(SELECT tc.ResTestCaseID
					 FROM ResTestCase tc WITH (NOLOCK)
					 WHERE tc.ResTestSetID IN
						(SELECT ts.ResTestSetID
						FROM ResTestSet ts WITH (NOLOCK)
						WHERE ts.RunsID = @RunsID
						)
					)
				)

	DELETE FROM ResTestBaseStateData
	WHERE ResTestSetID IN
				(SELECT ts.ResTestSetID
				   FROM ResTestSet ts WITH (NOLOCK)
				   WHERE ts.RunsID = @RunsID
				)

	DELETE FROM ResTestParam
	WHERE ResTestStepID IN
				(SELECT tp.ResTestStepID
				   FROM ResTestStep tp WITH (NOLOCK)
				   WHERE tp.ResTestCaseID IN
					(SELECT tc.ResTestCaseID
					 FROM ResTestCase tc WITH (NOLOCK)
					 WHERE tc.ResTestSetID IN
						(SELECT ts.ResTestSetID
						FROM ResTestSet ts WITH (NOLOCK)
						WHERE ts.RunsID = @RunsID
						)
					)
				)

	DELETE FROM ResTestStep
	WHERE ResTestCaseID IN
			(SELECT tc.ResTestCaseID
				FROM ResTestCase tc WITH (NOLOCK)
				WHERE tc.ResTestSetID IN
				(SELECT ts.ResTestSetID
				FROM ResTestSet ts WITH (NOLOCK)
				WHERE ts.RunsID = @RunsID
				)
			)

	DELETE FROM ResTestCase
	WHERE ResTestSetID IN
			(SELECT ts.ResTestSetID
			FROM ResTestSet ts WITH (NOLOCK)
			WHERE ts.RunsID = @RunsID
			)

	DELETE FROM ResTestSet
	WHERE RunsID = @RunsID


	RETURN(0)
END
GO

/* 
	Returns list of Runs
*/
IF (object_id('dbo.PortalGetRunsList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetRunsList
GO
CREATE PROCEDURE dbo.PortalGetRunsList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.RunsID as 'ID'
		,p.RunName as 'Name'
		,(CASE p.[InProgress]
			WHEN 1 THEN 'In Progress'
			ELSE 'Scheduled' 
			END
		 ) as 'Status'
		,p.Scheduled as 'Scheduled For'
		,(SELECT ISNULL(c.RunName, '') FROM DefRun c  WITH (NOLOCK) WHERE c.DefRunID = p.DefRunID)
			as 'Run Definition'
		,p.TestCaseCount as 'Test Cases'
		,ISNULL(p.TestCasesCompleted, '') as 'Completed'
		,p.LastUpdated as 'Last Update'
		,p.CreatedBy as 'Created By'
		,p.CreatedDate as 'Created'
	FROM dbo.Runs p WITH (NOLOCK)
	WHERE p.Active=1 and ISNULL(p.ResultsPosted,0) <> 1
	ORDER BY p.[Started] ASC, p.Scheduled
END
GO

/* 
	Returns specific Runs item
*/
IF (object_id('dbo.PortalGetRunsDetail')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetRunsDetail
GO
CREATE PROCEDURE dbo.PortalGetRunsDetail
(
		@ID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.RunName as 'Name'
		,p.[InProgress] as 'Status'
		,p.Scheduled as 'Scheduled'
		,p.DefRunID as 'RunDefID'
		,p.TestCaseCount as 'TestCaseCount'
		,p.TestCasesCompleted as 'TestCasesCompleted'
		,p.LastUpdated as 'LastUpdated'
		,p.CreatedBy as 'CreatedBy'
		,p.CreatedDate as 'CreatedDate'
	FROM dbo.Runs p WITH (NOLOCK)
	WHERE p.RunsID = @ID
END
GO

/* 
	Returns list of Runs completed (Journal)
*/
IF (object_id('dbo.PortalGetCompletedRunsList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetCompletedRunsList
GO
CREATE PROCEDURE dbo.PortalGetCompletedRunsList
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 p.RunsID as 'ID'
		,p.RunName as 'Name'
		,p.Scheduled as 'Scheduled For'
		,(CONVERT(VARCHAR(12), p.RunTimeSecs /60/60/24) + 'd ' 
			+ CONVERT(VARCHAR(12), p.RunTimeSecs /60/60 % 24) 
			+ ':' + RIGHT('0' + CONVERT(VARCHAR(2),  p.RunTimeSecs /60 % 60), 2) 
			+ ':' + RIGHT('0' + CONVERT(VARCHAR(2),  p.RunTimeSecs % 60), 2)
			+ 's'
		 ) as 'RunTime'
		,p.TestCaseCount as 'Test Cases'
		,(SELECT COUNT(ResTestCase.ResTestCaseID) FROM ResTestCase WITH (NOLOCK)
			WHERE ResTestCase.Result = 'Pass' AND ResTestCase.ResTestSetID IN 
			(SELECT ResTestSet.ResTestSetID FROM ResTestSet WITH (NOLOCK)
			WHERE ResTestSet.RunsID = p.RunsID)
			) as 'Passed'
		,(SELECT COUNT(ResTestCase.ResTestCaseID) FROM ResTestCase WITH (NOLOCK)
			WHERE ResTestCase.Result = 'Fail' AND ResTestCase.ResTestSetID IN 
			(SELECT ResTestSet.ResTestSetID FROM ResTestSet WITH (NOLOCK)
			WHERE ResTestSet.RunsID = p.RunsID)
			) as 'Fail'
		,(SELECT COUNT(ResTestCase.ResTestCaseID) FROM ResTestCase WITH (NOLOCK)
			WHERE (ResTestCase.Result NOT IN ('Fail', 'Pass')) AND ResTestCase.ResTestSetID IN 
			(SELECT ResTestSet.ResTestSetID FROM ResTestSet WITH (NOLOCK)
			WHERE ResTestSet.RunsID = p.RunsID)
			) as 'Error'
		,p.Completed as 'Completed'
		,p.CreatedBy as 'Created By'
	FROM dbo.Runs p WITH (NOLOCK)
	WHERE p.Active=1 and ISNULL(p.ResultsPosted,0) = 1
	ORDER BY p.Completed ASC
END
GO

/* 
	Returns specific Runs item
*/
IF (object_id('dbo.PortalGetCompletedRunsTestCaseList')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetCompletedRunsTestCaseList
GO
CREATE PROCEDURE dbo.PortalGetCompletedRunsTestCaseList
(
		@RunsID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT ResTestCase.ResTestCaseID as 'ID'
		,(ISNULL(ResTestCase.Result, 'No Run')
		 ) as 'Result'
		,(SELECT DefTestCase.TestCaseName
			FROM DefTestCase WITH (NOLOCK)
			WHERE DefTestCase.DefTestCaseID = ResTestCase.DefTestCaseID
		 ) as 'Test Case Name'
		,ResTestSet.TestSetName as 'Test Set'
		,ResTestSet.BaseStateName as 'Base State'
		,ResTestSet.TestMachineGroupName as 'Test Machine Group'
		,ISNULL(
		(SELECT tds.TestDataSetName 
			FROM DefTestDataSet tds WITH (NOLOCK)
			WHERE tds.DefTestDataSetID = 
			(SELECT tdsd.DefTestDataSetID
				FROM DefTestDataSetData tdsd WITH (NOLOCK)
				WHERE tdsd.DefTestDataSetDataID = ResTestCase.DefTestDataSetDataID
			)
		), '(none)') as 'Test Data Set'
	
	FROM ResTestCase WITH (NOLOCK)
	LEFT JOIN ResTestSet WITH (NOLOCK)
		ON ResTestCase.ResTestSetID = ResTestSet.ResTestSetID
	LEFT JOIN DefRunData WITH (NOLOCK)
		ON ResTestSet.DefRunDataID = DefRunData.DefRunDataID
	WHERE ResTestSet.RunsID = @RunsID
	ORDER BY ResTestCase.ResTestSetID, ResTestCase.[Order]

END
GO

/* 
	Returns specific Test Case results
*/
IF (object_id('dbo.PortalGetCompletedTestCase')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetCompletedTestCase
GO
CREATE PROCEDURE dbo.PortalGetCompletedTestCase
(
		@ResTestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT   DefTestCase.DefTestCaseID as 'DefTestCaseID'
			,DefTestCase.TestCaseName as 'Name'
			,DefTestCase.QCID as 'QCID'
			,DefTestCase.Description as 'Description'
			,(CASE ISNULL(DefTestCase.[TargetSystem],'null')
				WHEN 'null' THEN 'NetSight Meter'
				ELSE
				DefTestCase.[TargetSystem]
				END
				) as 'TargetSystem'
			,(ISNULL(DefTestCase.[MaxExecutionTimeMins],-1)
				) as 'MaxExecutionTimeMins'
					
			,DefTestCase.BaseState as 'Base State'
			,DefTestCase.LastUpdatedDate as 'Imported Date'
			,DefTestCase.LastUpdatedBy as 'Imported By'
			-- Following are the result details
			,ResTestCase.Result as 'Result'
			,ResTestCase.Evidence as 'Evidence'
			,ResTestCase.TestMachineUsed as 'TestMachineUsed'
			,ResTestCase.RunTimeSecs as 'RunTimeSecs'
			,ResTestCase.ErrorMessage as 'ErrorMessage'
			,ResTestSet.TestSetName as 'Test Set'
			,ResTestSet.BaseStateName as 'Base State'
			,ResTestSet.TestMachineGroupName as 'Test Machine Group'
			,ISNULL(ResTestSet.TestDataSetName, '(none)') as 'Test Data Set'

		FROM ResTestCase
			WITH (NOLOCK)
		LEFT JOIN DefTestCase WITH (NOLOCK)
			ON ResTestCase.DefTestCaseID = DefTestCase.DefTestCaseID
		LEFT JOIN ResTestSet WITH (NOLOCK)
			ON ResTestCase.ResTestSetID = ResTestSet.ResTestSetID
		WHERE ResTestCase.ResTestCaseID = @ResTestCaseID
END
GO

/* 
	Returns specific Test Case Steps Metadata
*/
IF (object_id('dbo.PortalGetCompletedTestStepsData')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetCompletedTestStepsData
GO
CREATE PROCEDURE dbo.PortalGetCompletedTestStepsData
(
		@ResTestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

		SELECT
			 ResTestStep.ResTestStepID
				as 'ResTestStepID'
			,ResTestStep.DefTestStepID
				as 'DefTestStepID'
			,ResTestStep.StepName
				as'StepName'
			,ResTestStep.ActionWordName
				as 'ActionWordName'
			,ResTestStep.SeqNo
				as 'SeqNo'
			,ResTestStep.Result
			,ResTestStep.RunTimeSecs
			,ResTestStep.Evidence
			,ResTestStep.ResultText
		FROM ResTestStep
			WITH (NOLOCK)
		WHERE ResTestStep.ResTestCaseID = @ResTestCaseID
		ORDER BY ResTestStep.SeqNo

END
GO

/* 
	Returns specific Test Case steps
*/
IF (object_id('dbo.PortalGetCompletedTestCaseSteps')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetCompletedTestCaseSteps
GO
CREATE PROCEDURE dbo.PortalGetCompletedTestCaseSteps
(
		@ResTestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TestStepsResults TABLE
	(
		lColResult varchar(2048) NULL,
		lCol01 varchar(2048) NULL,
		lCol02 varchar(2048) NULL,
		lCol03 varchar(2048) NULL,
		lCol04 varchar(2048) NULL,
		lCol05 varchar(2048) NULL,
		lCol06 varchar(2048) NULL,
		lCol07 varchar(2048) NULL,
		lCol08 varchar(2048) NULL,
		lCol09 varchar(2048) NULL,
		lCol10 varchar(2048) NULL,
		lCol11 varchar(2048) NULL,
		lCol12 varchar(2048) NULL,
		lCol13 varchar(2048) NULL,
		lCol14 varchar(2048) NULL,
		lCol15 varchar(2048) NULL,
		lCol16 varchar(2048) NULL,
		lCol17 varchar(2048) NULL,
		lCol18 varchar(2048) NULL,
		lCol19 varchar(2048) NULL,
		lCol20 varchar(2048) NULL
	)
	DECLARE @DefTestStepTmp TABLE
	(
		ResTestStepID [int] NOT NULL,
		DefTestStepID [int] NOT NULL,
		StepName [varchar](255) NOT NULL,
		ActionWordName [varchar](255) NOT NULL,
		stepSeqNo [int] NOT NULL,
		Result [varchar](10) NOT NULL,
		Evidence [bit] NOT NULL,
		RunTimeSecs [int] NULL
	)
	DECLARE @DefTestParamTmp TABLE
	(
		paramDefTestStepID [int] NOT NULL,
		paramSeqNo [int] NOT NULL,
		ParamName [varchar](255) NOT NULL,
		ParamData [varchar](2048) NOT NULL
	)

	--- Get the steps
	INSERT INTO @DefTestStepTmp
		SELECT
			 ResTestStep.ResTestStepID
				as 'ResTestStepID'
			,ResTestStep.DefTestStepID
				as 'DefTestStepID'
			,ResTestStep.StepName
				as'StepName'
			,ResTestStep.ActionWordName
				as 'ActionWordName'
			,ResTestStep.SeqNo
				as 'SeqNo'
			,ResTestStep.Result
			,ResTestStep.Evidence
			,ResTestStep.RunTimeSecs
		FROM ResTestStep
			WITH (NOLOCK)
		WHERE ResTestStep.ResTestCaseID = @ResTestCaseID
		ORDER BY ResTestStep.SeqNo

	--- Get the parameters
	INSERT INTO @DefTestParamTmp
		SELECT
			p.ResTestStepID,
			p.SeqNo,
			p.Name,
			p.Value
		FROM ResTestParam p
			WITH (NOLOCK)
		WHERE p.ResTestStepID IN (SELECT ResTestStepID FROM @DefTestStepTmp)

	-- Build the table
	DECLARE @StepSeq int = 0, @StepID int
	DECLARE @pActionWordName varchar(255), @pStepName varchar(255)
	DECLARE @pResult varchar(10), @pRunTimeSecs int
	-- Iterate through the steps
	WHILE (1 = 1) 
	BEGIN  
		-- Get next Step
		SELECT TOP 1	 @StepSeq = ts.stepSeqNo
						,@StepID = ts.ResTestStepID
						,@pActionWordName = ts.ActionWordName
						,@pStepName = ts.StepName
						,@pResult = ts.Result
						,@pRunTimeSecs = ts.RunTimeSecs
		FROM @DefTestStepTmp ts
		WHERE ts.stepSeqNo > @StepSeq
		ORDER BY ts.stepSeqNo

		-- Exit loop if no more steps
		IF @@ROWCOUNT = 0 BREAK;

		--- first row is results, next is step name, and then param headers
		INSERT INTO @TestStepsResults
		VALUES
		(
			 @pResult
			,@pStepName
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 1)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 2)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 3)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 4)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 5)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 6)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 7)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 8)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 9)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 10)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 11)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 12)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 13)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 14)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 15)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 16)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 17)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 18)
			,(SELECT ParamName FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 19)
		)
		--- second row is run time, action word name and then param data
		INSERT INTO @TestStepsResults
		VALUES
		(
			 ('Secs:' + CONVERT(varchar(255), @pRunTimeSecs))
			,@pActionWordName
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 1)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 2)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 3)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 4)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 5)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 6)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 7)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 8)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 9)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 10)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 11)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 12)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 13)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 14)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 15)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 16)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 17)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 18)
			,(SELECT ParamData FROM @DefTestParamTmp WHERE paramDefTestStepID = @StepID AND paramSeqNo = 19)
		)
	END

	--- Return the results
	SELECT * FROM @TestStepsResults

END
GO

/*
	get test evidence meta-data for ResTestCase
*/
IF (object_id('dbo.PortalGetTestResultEvidence')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetTestResultEvidence
GO
CREATE PROCEDURE dbo.PortalGetTestResultEvidence
(
	@ResTestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		[ResTestEvidenceID],
		[SourceID],
		[SourceTable],
		[Name],
		[DataType],
		[FileName],
		[Description]
	FROM ResTestEvidence WITH (NOLOCK)
	WHERE
		SourceID = @ResTestCaseID
		AND SourceTable = 'ResTestCase'

END
GO

/*
	get test evidence meta-data for ResTestStep
*/
IF (object_id('dbo.PortalGetStepResultEvidence')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetStepResultEvidence
GO
CREATE PROCEDURE dbo.PortalGetStepResultEvidence
(
	@ResTestStepID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		[ResTestEvidenceID],
		[SourceID],
		[SourceTable],
		[Name],
		[DataType],
		[FileName],
		[Description]
	FROM ResTestEvidence WITH (NOLOCK)
	WHERE
		SourceID = @ResTestStepID
		AND SourceTable = 'ResTestStep'

END
GO

/*
	get test evidence binary for evidence record
*/
IF (object_id('dbo.PortalGetResultEvidenceBinary')) IS NOT NULL
	DROP PROCEDURE dbo.PortalGetResultEvidenceBinary
GO
CREATE PROCEDURE dbo.PortalGetResultEvidenceBinary
(
	@ResTestEvidenceID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		[Data]
	FROM ResTestEvidence WITH (NOLOCK)
	WHERE
		ResTestEvidenceID = @ResTestEvidenceID
END
GO

/* ----------------------------------------------------------
	Following used by the service
   ----------------------------------------------------------
*/

/* 
	test machine list
*/
IF (object_id('dbo.ServiceGetTestMachineList')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetTestMachineList
GO
CREATE PROCEDURE dbo.ServiceGetTestMachineList
AS
BEGIN
	SET NOCOUNT ON

	SELECT *
	FROM [dbo].[DefTestMachines] WITH (NOLOCK)
END
GO

/* 
	runs list
*/
IF (object_id('dbo.ServiceGetRunsList')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetRunsList
GO
CREATE PROCEDURE dbo.ServiceGetRunsList
AS
BEGIN
	SET NOCOUNT ON

	SELECT *
	FROM [dbo].Runs WITH (NOLOCK)
	WHERE Active = 1 AND ISNULL(ResultsPosted,0) = 0
END
GO

/*
	start run
*/
IF (object_id('dbo.ServiceStartRun')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceStartRun
GO
CREATE PROCEDURE dbo.ServiceStartRun
(
		@RunsID int
)
AS
BEGIN
	SET NOCOUNT ON

	--- Have the test cases already been built out?
	IF EXISTS (SELECT 1 FROM dbo.ResTestSet WITH (NOLOCK) WHERE RunsID = @RunsID)
	BEGIN
		RETURN
	END

	INSERT INTO ResTestSet
	(
		 [DefTestSetID]
		,[RunsID]
		,[DefRunDataID]
		,[LastUpdated]
		,[TestCaseCount]
		,[DefRunName]
		,[BaseStateName]
		,[TestSetName]
		,[TestMachineGroupName]
		,[ExecutionOrder]
		,[MachineOp]
	)
	SELECT 
		 DefRunData.DefTestSetID
		,Runs.RunsID
		,DefRunData.DefRunDataID
		,GETDATE()
		,(SELECT COUNT(DefTestSetCases.DefTestSetCasesID) FROM DefTestSetCases WITH (NOLOCK) 
			WHERE DefRunData.DefTestSetID = DefTestSetCases.DefTestSetID
			) as 'TestCaseCount'
		,(SELECT DefRun.RunName FROM DefRun WITH (NOLOCK)
			WHERE Runs.DefRunID = DefRun.DefRunID
			) as 'DefRunName'
		,(SELECT DefBaseState.BaseStateName FROM DefBaseState WITH (NOLOCK)
			WHERE DefRunData.DefBaseStateID = DefBaseState.DefBaseStateID
			) as 'BaseStateName'
		,(SELECT DefTestSet.TestSetName FROM DefTestSet WITH (NOLOCK)
			WHERE DefRunData.DefTestSetID = DefTestSet.DefTestSetID
			) as 'TestSetName'
		,(SELECT DefTestMachineGroup.TestMachineGroupName FROM DefTestMachineGroup WITH (NOLOCK)
			WHERE DefRunData.DefTestMachineGroupID = DefTestMachineGroup.DefTestMachineGroupID
			) as 'TestMachineGroupName'
		,(SELECT ListItemValue FROM DefListItems WITH (NOLOCK)
			WHERE DefListItems.DefListItemsID =
				(SELECT DefTestSet.ExecutionOrder FROM DefTestSet WITH (NOLOCK)
					WHERE DefRunData.DefTestSetID = DefTestSet.DefTestSetID)
			) as 'ExecutionOrder'
		,(SELECT ListItemValue FROM DefListItems WITH (NOLOCK)
			WHERE DefListItems.DefListItemsID =
				(SELECT DefTestSet.MachineOp FROM DefTestSet WITH (NOLOCK)
					WHERE DefRunData.DefTestSetID = DefTestSet.DefTestSetID)
			) as 'MachineOp'
	FROM Runs WITH (NOLOCK)
	LEFT JOIN DefRunData WITH (NOLOCK) ON Runs.DefRunID = DefRunData.DefRunID
	WHERE Runs.RunsID = @RunsID

	INSERT INTO ResTestCase
	(
		 [ResTestSetID]
		,[DefTestCaseID]
		,[Order]
		,[MaxExecutionTimeMins]
 		,[DefTestDataSetDataID]
	)
	SELECT
		 ResTestSet.ResTestSetID
		,DefTestCase.DefTestCaseID
		,DefTestSetCases.[Order]
		,DefTestCase.MaxExecutionTimeMins
		,DefTestDataSetData.DefTestDataSetDataID
	FROM ResTestSet WITH (NOLOCK)
	FULL JOIN DefTestSetCases WITH (NOLOCK) ON ResTestSet.DefTestSetID = DefTestSetCases.DefTestSetID
	LEFT JOIN DefTestCase WITH (NOLOCK) ON DefTestSetCases.DefTestCaseID = DefTestCase.DefTestCaseID
	FULL JOIN DefTestDataSetData WITH (NOLOCK) ON DefTestDataSetData.DefTestDataSetID = DefTestSetCases.DefTestDataSetID
	WHERE ResTestSet.RunsID = @RunsID
	ORDER BY ResTestSet.ResTestSetID, DefTestSetCases.[Order]

	DECLARE @TestCaseCount int
	SELECT @TestCaseCount = COUNT(c.ResTestCaseID) FROM ResTestCase c WITH (NOLOCK) 
	FULL JOIN ResTestSet c2 WITH (NOLOCK) ON c.ResTestSetID = c2.ResTestSetID
	WHERE c2.RunsID = @RunsID

	UPDATE dbo.Runs
	SET 
		 TestCaseCount = @TestCaseCount
		,[Started] = GETDATE()
		,LastUpdated = GETDATE()
	WHERE RunsID = @RunsID

END
GO

/*
	start run
*/
IF (object_id('dbo.ServiceGetTestListForRun')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetTestListForRun
GO
CREATE PROCEDURE dbo.ServiceGetTestListForRun
(
		@RunsID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		 ResTestCase.ResTestCaseID as 'Key'
		,ResTestCase.Identifier as 'Guid'
		,ResTestSet.RunsID as 'RunKey'
		,Runs.Identifier as 'RunGuid'
		,Runs.DefRunID as 'DefRunKey'
		,ResTestSet.ResTestSetID as 'TestSetKey'
		,ResTestSet.Identifier as 'TestSetGuid'
		,ResTestCase.DefTestCaseID as 'DefTestCaseID'
		,(SELECT deftc.TestCaseName
			FROM DefTestCase deftc WITH (NOLOCK)
			WHERE deftc.DefTestCaseID = ResTestCase.DefTestCaseID
		 ) as 'TestCaseName'
		,(SELECT deftc.QCID
			FROM DefTestCase deftc WITH (NOLOCK)
			WHERE deftc.DefTestCaseID = ResTestCase.DefTestCaseID
		 ) as 'TestQCID'
		,(SELECT COUNT(deftc.DefTestCaseID)
			FROM DefTestCase deftc WITH (NOLOCK)
			WHERE deftc.TestCaseName = 
				(SELECT TestCaseName FROM DefTestCase deftc2 WITH (NOLOCK)
					WHERE deftc2.DefTestCaseID = ResTestCase.DefTestCaseID)
		 ) as 'TestCaseRevision'
		,ResTestCase.[Order] as 'Order'
		,ResTestCase.MaxExecutionTimeMins as 'MaxExecutionTimeMins'
		,(SELECT deftc.TargetSystem
			FROM DefTestCase deftc WITH (NOLOCK)
			WHERE deftc.DefTestCaseID = ResTestCase.DefTestCaseID
		 ) as 'TargetSystem'
		,(
			STUFF( (SELECT ',' + cast( DefTestMachineGroupData.DefTestMachinesID as varchar)
										 FROM DefTestMachineGroupData
										 WHERE EXISTS ( SELECT 1 
														FROM DefTestMachines
														WITH (NOLOCK)
														WHERE DefTestMachines.DefTestMachinesID = DefTestMachineGroupData.DefTestMachinesID
														)
											AND DefTestMachineGroupData.DefTestMachineGroupID = DefRunData.DefTestMachineGroupID
										 ORDER BY DefTestMachineGroupData.DefTestMachinesID
										 FOR XML PATH('')), 
										1, 1, '')	 
			)  as 'TestMachineGroup'
		,ResTestSet.ExecutionOrder as 'ExecutionOrder'
		,ResTestSet.MachineOp as 'MachineOp'
		,ResTestSet.DefRunDataID as 'DefRunDataID'
		,ResTestCase.DefTestDataSetDataID as 'TestDataSetDataKey'
		,ResTestCase.InProgress as 'InProgress'
		,ResTestCase.Completed as 'Completed'
		,ResTestCase.ResultsPosted as 'ResultsPosted'
	FROM ResTestCase
	LEFT JOIN ResTestSet WITH (NOLOCK)
		ON ResTestSet.ResTestSetID = ResTestCase.ResTestSetID
	LEFT JOIN DefRunData WITH (NOLOCK)
		ON DefRunData.DefRunDataID = ResTestSet.DefRunDataID
	LEFT JOIN Runs WITH (NOLOCK)
		ON Runs.RunsID = ResTestSet.RunsID
	WHERE ResTestCase.ResTestSetID = ResTestSet.ResTestSetID
		AND ResTestSet.RunsID = @RunsID
	ORDER BY ResTestSet.ResTestSetID, ResTestCase.[Order]

END
GO

IF (object_id('dbo.ServiceGetParamListForBaseState')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetParamListForBaseState
GO
CREATE PROCEDURE dbo.ServiceGetParamListForBaseState
(
		@ResTestSetID int,
		@DefRunDataID int
)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT 1 FROM ResTestBaseStateData WITH (NOLOCK)
					WHERE ResTestSetID = @ResTestSetID)
	BEGIN
		INSERT INTO ResTestBaseStateData
		(
			 [ResTestSetID]
			,[ParamName]
			,[ParamValue]
		)
		SELECT 
			 @ResTestSetID
			,BaseStateParams.BaseStateParamName as 'ParamName'
			,DefBaseStateData.ParamValue as 'ParamValue'
		FROM DefBaseState WITH (NOLOCK)
		FULL JOIN DefBaseStateData WITH (NOLOCK) ON
			DefBaseStateData.DefBaseStateID = DefBaseState.DefBaseStateID
		LEFT JOIN BaseStateParams WITH (NOLOCK) ON
			BaseStateParams.BaseStateParamsID = DefBaseStateData.BaseStateParamsID
		WHERE DefBaseState.DefBaseStateID =
			(SELECT DefBaseStateID FROM DefRunData WITH (NOLOCK) WHERE DefRunDataID = @DefRunDataID)
	END

	DECLARE @BaseStateName varchar(255)
	SELECT @BaseStateName = ResTestSet.BaseStateName FROM ResTestSet WITH (NOLOCK)
		WHERE ResTestSet.ResTestSetID = @ResTestSetID

	SELECT
		 @BaseStateName as 'Name'
		,ParamName
		,ParamValue
	FROM ResTestBaseStateData WITH (NOLOCK)
	WHERE ResTestBaseStateData.ResTestSetID = @ResTestSetID

END
GO

IF (object_id('dbo.ServiceGetListValuesConcat')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetListValuesConcat
GO
CREATE PROCEDURE dbo.ServiceGetListValuesConcat
(
		@ListName varchar(255)
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
	(
		STUFF( (SELECT ';' +  DefListItems.ListItemValue
		FROM DefListItems WITH(NOLOCK)
		WHERE DefListItems.DefListID =
			(SELECT DefList.DefListID
				FROM DefList WITH(NOLOCK)
				WHERE DefList.ListName = @ListName)
											 ORDER BY DefListItems.ListItemValue
											 FOR XML PATH('')), 
											1, 1, '')	 
	)

END
GO

IF (object_id('dbo.ServiceGetListValueForParam')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetListValueForParam
GO
CREATE PROCEDURE dbo.ServiceGetListValueForParam
(
		@ListName varchar(255)
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT DefListItems.ListItemValue
		FROM DefListItems WITH(NOLOCK)
		WHERE DefListItems.DefListID =
			(SELECT DefList.DefListID
				FROM DefList WITH(NOLOCK)
				WHERE DefList.ListName = @ListName)
END
GO

/*
	returns test data row
*/
IF (object_id('dbo.ServiceGetTestDataSetRow')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetTestDataSetRow
GO
CREATE PROCEDURE dbo.ServiceGetTestDataSetRow
(
		@DefTestDataSetDataID int
)
AS
BEGIN
	SET NOCOUNT ON

SELECT 
		 td.DefTestDataSetID as 'ID'
		,td.TestDataSetName as 'Name'
		,(SELECT COUNT(c.DefTestDataSetID)
			FROM DefTestDataSet c WITH (NOLOCK)
			WHERE c.TestDataSetName = td.TestDataSetName
		 ) as 'Revision'
		,td.[Description] as 'Description'
		,(tds.SeqNo + 1) as 'RowNumber'
		,(SELECT COUNT(c.DefTestDataSetDataID)
			FROM DefTestDataSetData c WITH (NOLOCK)
			WHERE c.DefTestDataSetID = td.DefTestDataSetID
		 ) as 'RowCount'
		,td.DataPointName01
		,tds.DataPointData01
		,td.DataPointName02
		,tds.DataPointData02
		,td.DataPointName03
		,tds.DataPointData03
		,td.DataPointName04
		,tds.DataPointData04
		,td.DataPointName05
		,tds.DataPointData05
		,td.DataPointName06
		,tds.DataPointData06
		,td.DataPointName07
		,tds.DataPointData07
		,td.DataPointName08
		,tds.DataPointData08
		,td.DataPointName09
		,tds.DataPointData09
	FROM DefTestDataSetData tds WITH (NOLOCK)
	LEFT JOIN DefTestDataSet td WITH (NOLOCK)
		ON td.DefTestDataSetID = tds.DefTestDataSetID
	WHERE tds.DefTestDataSetDataID = @DefTestDataSetDataID

END
GO


/*
	returns list of steps and parameters for a testcase
*/
IF (object_id('dbo.ServiceGetTestSteps')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceGetTestSteps
GO
CREATE PROCEDURE dbo.ServiceGetTestSteps
(
		@DefTestCaseID int
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		 DefTestStep.DefTestStepID as 'Key'
		,DefTestStep.StepName as 'Name'
		,ActionWords.ActionWordName as 'Actionword'
		,ActionWords.ActionWordType as 'Type'
		,DefTestParam.ParamName as 'ParamName'
		,DefTestParam.ParamData as 'ParamValue'
	FROM DefTestStep WITH (NOLOCK)
	LEFT JOIN ActionWords WITH (NOLOCK)
		ON ActionWords.ActionWordID = DefTestStep.ActionWordID
	FULL JOIN DefTestParam WITH (NOLOCK)
		ON DefTestParam.DefTestStepID = DefTestStep.DefTestStepID
	WHERE DefTestCaseID = @DefTestCaseID
	ORDER BY DefTestStep.SeqNo, DefTestParam.SeqNo

END
GO

/*
	set test to in progress
*/
IF (object_id('dbo.ServiceStartTest')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceStartTest
GO
CREATE PROCEDURE dbo.ServiceStartTest
(
		@ResTestCaseID int
	   ,@RunsID int
)
AS
BEGIN
	SET NOCOUNT ON

	UPDATE dbo.ResTestCase
	SET 
		 InProgress = 1
	WHERE ResTestCaseID = @ResTestCaseID

	UPDATE dbo.Runs
	SET 
	    InProgress = 1
	   ,LastUpdated = GETDATE()
	WHERE RunsID = @RunsID

END
GO

/*
	set test execution complete
*/
IF (object_id('dbo.ServiceEndTest')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceEndTest
GO
CREATE PROCEDURE dbo.ServiceEndTest
(
		@ResTestCaseID int
	   ,@RunsID int
	   ,@TestMachineUsed varchar(1024)
)
AS
BEGIN
	SET NOCOUNT ON

	UPDATE dbo.ResTestCase
	SET 
		 Completed = 1
		,TestMachineUsed = @TestMachineUsed
	WHERE ResTestCaseID = @ResTestCaseID

	DECLARE @TestCaseCount int
	SELECT @TestCaseCount = COUNT(c.ResTestCaseID) FROM ResTestCase c WITH (NOLOCK) 
	FULL JOIN ResTestSet c2 WITH (NOLOCK) ON c.ResTestSetID = c2.ResTestSetID
	WHERE c2.RunsID = @RunsID AND ISNULL(c.Completed,0) = 1

	UPDATE dbo.Runs
	SET 
		 TestCasesCompleted = @TestCaseCount
	    ,LastUpdated = GETDATE()
	WHERE RunsID = @RunsID

END
GO

/*
	reset active test
*/
IF (object_id('dbo.ServiceResetTest')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceResetTest
GO
CREATE PROCEDURE dbo.ServiceResetTest
(
		@ResTestCaseID int
	   ,@RunsID int
)
AS
BEGIN
	SET NOCOUNT ON

	UPDATE dbo.ResTestCase
	SET 
		 InProgress = 0
		,Completed = 0
		,TestMachineUsed = null
	WHERE ResTestCaseID = @ResTestCaseID

	DECLARE @TestCaseCount int
	SELECT @TestCaseCount = COUNT(c.ResTestCaseID) FROM ResTestCase c WITH (NOLOCK) 
	FULL JOIN ResTestSet c2 WITH (NOLOCK) ON c.ResTestSetID = c2.ResTestSetID
	WHERE c2.RunsID = @RunsID AND ISNULL(c.Completed,0) = 1

	UPDATE dbo.Runs
	SET 
		 TestCasesCompleted = @TestCaseCount
	    ,LastUpdated = GETDATE()
	WHERE RunsID = @RunsID

END
GO


/*
	post test step results
*/
IF (object_id('dbo.ServiceTestStepResult')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceTestStepResult
GO
CREATE PROCEDURE dbo.ServiceTestStepResult
(
	@ResTestCaseID int,
	@DefTestStepID int,
	@StepName varchar(255),
	@ActionWordName varchar(255),
	@ResultText varchar(max),
	@Result varchar(30),
	@RunTimeSecs int,
	@Evidence bit,
	@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO ResTestStep
	(
		ResTestCaseID,
		DefTestStepID,
		StepName,
		ActionWordName,
		SeqNo,
		ResultText,
		Result,
		RunTimeSecs,
		Evidence
	)
	VALUES
	(
		@ResTestCaseID,
		@DefTestStepID,
		@StepName,
		@ActionWordName,
		(SELECT SeqNo FROM DefTestStep WITH (NOLOCK) WHERE DefTestStepID = @DefTestStepID),
		@ResultText,
		@Result,
		@RunTimeSecs,
		@Evidence
	)
	SET @OutIdentity = SCOPE_IDENTITY()

END
GO

/*
	post test step results
*/
IF (object_id('dbo.ServiceTestStepResultParam')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceTestStepResultParam
GO
CREATE PROCEDURE dbo.ServiceTestStepResultParam
(
	@ResTestStepID int,
	@SeqNo int,
	@Name varchar(255),
	@Value varchar(max),
	@OutIdentity int = NULL OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO ResTestParam
	(
		ResTestStepID,
		SeqNo,
		Name,
		Value
	)
	VALUES
	(
		@ResTestStepID,
		@SeqNo,
		@Name,
		@Value
	)
	SET @OutIdentity = SCOPE_IDENTITY()

END
GO

/*
	set test complete
*/
IF (object_id('dbo.ServiceSetTestComplete')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceSetTestComplete
GO
CREATE PROCEDURE dbo.ServiceSetTestComplete
(
		@ResTestCaseID int
	   ,@RunsID int
	   ,@Result varchar(30)
	   ,@Evidence bit
)
AS
BEGIN
	SET NOCOUNT ON

	UPDATE dbo.ResTestCase
	SET 
		 ResultsPosted = 1
		,Evidence = @Evidence
		,Result = @Result
		,RunTimeSecs = (SELECT ISNULL(SUM(RunTimeSecs),0) FROM ResTestStep WITH (NOLOCK)
						WHERE ResTestCaseID = @ResTestCaseID)
	WHERE ResTestCaseID = @ResTestCaseID

	DECLARE @TestCaseCount int
	SELECT @TestCaseCount = COUNT(c.ResTestCaseID) FROM ResTestCase c WITH (NOLOCK) 
	FULL JOIN ResTestSet c2 WITH (NOLOCK) ON c.ResTestSetID = c2.ResTestSetID
	WHERE c2.RunsID = @RunsID AND ISNULL(c.Completed,0) = 1

	UPDATE dbo.Runs
	SET 
		 TestCasesCompleted = @TestCaseCount
	    ,LastUpdated = GETDATE()
	WHERE RunsID = @RunsID

END
GO

/*
	Check if a run is complete
*/
IF (object_id('dbo.ServiceCheckRunsComplete')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceCheckRunsComplete
GO
CREATE PROCEDURE dbo.ServiceCheckRunsComplete
(
	   @RunsID int
)
AS
BEGIN
	SET NOCOUNT ON

	IF NOT EXISTS (SELECT 1 FROM ResTestCase c WITH (NOLOCK) 
				FULL JOIN ResTestSet c2 WITH (NOLOCK)
					ON c.ResTestSetID = c2.ResTestSetID
				WHERE c2.RunsID = @RunsID AND ISNULL(c.ResultsPosted,0) = 0
			  )
	BEGIN
		--- all tests are complete, indicate as such
		DECLARE @Started datetime
		DECLARE @Completed datetime
		SELECT @Started = [Started], @Completed = GETDATE() 
			FROM dbo.Runs WITH (NOLOCK)
			WHERE RunsID = @RunsID
		UPDATE dbo.Runs
		SET 
			 ResultsPosted = 1
			,Completed = @Completed
			,RunTimeSecs = DATEDIFF(SECOND, @Started, @Completed)
		WHERE RunsID = @RunsID
	END
END
GO

/*
	set test in error status
*/
IF (object_id('dbo.ServiceSetTestError')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceSetTestError
GO
CREATE PROCEDURE dbo.ServiceSetTestError
(
		@ResTestCaseID int
	   ,@RunsID int
	   ,@ErrorMessage varchar(1024)
)
AS
BEGIN
	SET NOCOUNT ON

	UPDATE dbo.ResTestCase
	SET 
		 Completed = 1
	    ,ResultsPosted = 1
		,Result = 'Error'
		,RunTimeSecs = 0
		,ErrorMessage = @ErrorMessage
	WHERE ResTestCaseID = @ResTestCaseID

	DECLARE @TestCaseCount int
	SELECT @TestCaseCount = COUNT(c.ResTestCaseID) FROM ResTestCase c WITH (NOLOCK) 
	FULL JOIN ResTestSet c2 WITH (NOLOCK) ON c.ResTestSetID = c2.ResTestSetID
	WHERE c2.RunsID = @RunsID AND ISNULL(c.Completed,0) = 1

	UPDATE dbo.Runs
	SET 
		 TestCasesCompleted = @TestCaseCount
	    ,LastUpdated = GETDATE()
	WHERE RunsID = @RunsID
END
GO

/*
	post test evidence
*/
IF (object_id('dbo.ServiceTestResultEvidence')) IS NOT NULL
	DROP PROCEDURE dbo.ServiceTestResultEvidence
GO
CREATE PROCEDURE dbo.ServiceTestResultEvidence
(
	@SourceID int,
	@SourceTable varchar(50),
	@Name varchar(1024),
	@DataType varchar(50),
	@FileName varchar(2048),
	@Description varchar(max),
	@Data varbinary(max)
)
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO [ResTestEvidence]
	(
		[SourceID],
		[SourceTable],
		[Name],
		[DataType],
		[FileName],
		[Description],
		[Data]
	)
	VALUES
	(
		@SourceID,
		@SourceTable,
		@Name,
		@DataType,
		@FileName,
		@Description,
		@Data
	)

END
GO
